Imagine you have some text in a stored procedure somewhere and you can’t remember where it is or maybe it is in numerous places and you need a list of all the DBs and objects that contain it. This will help you find all the instances of your text.
One place this is useful is if you write a comment like “–TODO” where you need to make an amend (but are waiting on someone before you can make the change). Another example is your name or a reference to a table within a dynamic sql statement which is not picked up by dependencies. Anyway, I am sure you get the drift. You want to search all stored procedures for a string. There are some nice visual tools you can download to do this but if you cannot do this for some reason then here’s a useful script using an undocumented sp by MS – sp_MSforeachdb
Not surprisingly MSforeachdb carries out a command on every database on the server. This command uses the ? marker to indicate the current DB.
Stored procedures are made visible via the sys.sql_modules view. (http://msdn.microsoft.com/en-us/library/ms175081.aspx)
Combining these two gives you a powerful and flexible search mechanism.
-- David Bridge -- David Bridge Technology Limited -- February 2011 DECLARE @SearchArg varchar(200) = 'TODO' CREATE TABLE #temp (DB_name varchar(128), Object_name varchar(128)); DECLARE @sql varchar(1000) = 'USE [?]; INSERT INTO #temp SELECT db_name() as db_name ,object_name(object_id) as object_name FROM sys.sql_modules WHERE definition like ''%@SearchArg%''' SET @sql = REPLACE (@sql,'@SearchArg',@SearchArg) exec sp_MSforeachdb @sql SELECT * FROM #temp DROP TABLE #temp