0

Is there any way I can find how many stored procedures are created referencing My Database?

IF I change database name from My Database to No Database, how to modify all the stored procedures referencing to my OLD database name to new database name?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jay Nani
  • 105
  • 1
  • 1
  • 6
  • 1
    Possible duplicate of [Query to list all stored procedures](http://stackoverflow.com/questions/219434/query-to-list-all-stored-procedures) – techspider Aug 04 '16 at 15:38
  • Could help (manually) accomplish what you're after with a free search tool such as [Redgate SQL Search](http://www.red-gate.com/products/sql-development/sql-search/) – Mike Guthrie Aug 04 '16 at 15:51

1 Answers1

1

If your stored procedures actually have the text 'My Database' in them, you can use this, replacing 'ADB' with 'DatabaseName':

SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m 
INNER JOIN sys.objects o 
ON m.object_id=o.object_id
WHERE m.definition Like '%ABD%'
TTeeple
  • 2,913
  • 1
  • 13
  • 22
  • Thank you. This query brings up all the stored procedures. How to modify these stored procedures with my New Database Name ?? – Jay Nani Aug 04 '16 at 15:53
  • You could do a REPLACE using other sys views. I would recommend following Mike Guthrie's advice on using a search tool like Redgate SQL Search. You are able to modify stored procs with it as well. – TTeeple Aug 04 '16 at 17:05