As a SQL Server programmer, there have been many occasions when I needed to search through code I've previously written to find a particular word or phrase. This isn't too difficult to do using SQL Server's built-in Catalog Views. For example, a T-SQL statement that searches for a particular word in the AdventureWorks sample database might look like that in Listing 1. The main problem with this approach is that it works only within the context of the current database. Often, I need to search all the databases on a SQL Server instance to find a function or stored procedure I had written.

<strong>Listing 1: Traditional T-SQL Statement That Looks for a Word in Code<br></strong>  <br>USE ADVENTUREWORKS;<br>DECLARE @SEARCH_STRING VARCHAR(50) <br>SET @SEARCH_STRING = 'CheckDate'<br><br>SELECT<br> AS \\[Schema Name\\],<br> AS \\[Object Name\\],<br>  type_desc AS \\[Object Type\\],<br>  substring(definition,CHARINDEX(@SEARCH_STRING,definition)-30, 60)<br>    AS \\[Text Context\\]<br>FROM sys.objects<br>JOIN sys.sql_modules<br>  ON sys.objects.object_id = sys.sql_modules.object_id<br>JOIN sys.schemas<br>  ON sys.objects.schema_id = sys.schemas.schema_id<br>WHERE CHARINDEX(@SEARCH_STRING,sys.sql_modules.definition) > 0<br><br><br> 

I needed a way to apply the code in Listing 1 to every database. The best way to do this is using an undocumented stored procedure in the master database named sp_MSforeachdb. As its name implies, this stored procedure will execute a given T-SQL statement against every database on a SQL Server instance. The T-SQL statement is passed as a NVARCHAR(2000) parameter to sp_MSforeachdb.

The syntax for using sp_MSforeachdb is

EXEC sp_MSforeachdb 'T-SQL code here'

For example, suppose the current instance of SQL Server contains six databases: master, tempdb, model, msdb, AdventureWorksDW, and AdventureWorks. If you execute the code

EXEC sp_MSforeachdb 'SELECT getdate()'

six separate queries will run and you'll receive the current date and time for each database.

The sp_MSforeachdb stored procedure also provides an easy way to display or use the current database name within your executable statement: Any question mark (?) you place within the parameter is automatically replaced by the name of the current database. For example, the command

EXEC sp_MSforeachdb <br>  'print ''The current db is ?'''<br><br>

results in the following being displayed:

The current db is master<br>The current db is tempdb<br>The current db is model<br>The current db is msdb<br>The current db is AdventureWorksDW<br>The current db is AdventureWorks

By modifying the search code that works within a database (e.g., code like that in Listing 1) to use the sp_MSforeachdb stored procedure, you can create a stored procedure that searches through the code in all the databases. Listing 2 shows this stored procedure, which is named SearchThroughAllCode. As you can see in callout A, it takes advantage of the question mark so that each database on the SQL Server instance is searched.

<strong>Listing 2: SearchThroughAllCode.sql<br><br></strong><br>CREATE PROCEDURE \\[dbo\\].\\[SearchThroughAllCode\\]<br>  @searchStringIn varchar(255) AS<br>BEGIN<br>  SET NOCOUNT ON;<br>  DECLARE @SQL VARCHAR(2000)<br><strong>-- BEGIN CALLOUT A<br></strong>  SET @SQL = 'USE ?; <br>  SELECT ''?'' AS DATABASE_NAME, <br><strong>-- END CALLOUT A<br></strong> AS \\[Schema Name\\],<br> AS \\[Object Name\\] ,<br>    type_desc AS \\[Object Type\\],<br>    substring(definition,CHARINDEX(''' + @SEARCHSTRINGIN + ''',<br>      definition)-30, 60)<br>    AS \\[Text Context\\]<br>  FROM sys.objects<br>  JOIN sys.sql_modules<br>    ON sys.objects.object_id = sys.sql_modules.object_id<br>  JOIN sys.schemas<br>    ON sys.objects.schema_id = sys.schemas.schema_id<br>  WHERE CHARINDEX(''' + @SEARCHSTRINGIN + ''',definition)>0'<br><br>  CREATE TABLE #SEARCH_RESULTS<br>   ( DATABASE_NAME NVARCHAR(128),<br>    \\[SCHEMA_NAME\\] VARCHAR(128),<br>    \\[OBJECT_NAME\\] VARCHAR(128),<br>    \\[OBJECT_TYPE\\] VARCHAR(20),<br>    TEXT_CONTEXT VARCHAR(60) )<br>  INSERT INTO #SEARCH_RESULTS EXEC sp_MSforeachdb @SQL<br><br>  SELECT * FROM #SEARCH_RESULTS<br>  ORDER BY<br>  DATABASE_NAME, \\[SCHEMA_NAME\\], \\[OBJECT_NAME\\]<br>END<br><br><br> 

The syntax for the SearchThroughAllCode stored procedure is

EXECUTE \\[dbo\\].\\[SearchThroughAllCode\\]<br>  'SearchString'

where SearchString is the word or phrase you want to search for. So, for example, if you want to search for CheckDate, you'd use the command

EXECUTE \\[dbo\\].\\[SearchThroughAllCode\\]<br>  'CheckDate'<br>

Figure 1 shows sample results from running the stored procedure with this command.

You can download the SearchThroughAllCode stored procedure by clicking the Download the Code Here button near the top of the page. This download also includes another stored procedure named SearchThroughAllObjects. Using the same techniques as SearchThroughAllCode, SearchThroughAllObjects searches for words and phrases in tables, columns, synonyms, constraints, and linked servers as well as stored procedures, views, and functions. Like SearchThroughAllCode, SearchThroughAllObjects will search through each database on the SQL Server instance. The syntax for the SearchThroughAllObjects stored procedure is 

EXECUTE \\[dbo\\].\\[SearchThroughAllObjects\\]<br>  'SearchString'<br>

where SearchString is the word or phrase you want to search for. SearchThroughAllObjects takes a little longer to run than SearchThroughAllCode, but when you have to find something, it can be a lifesaver.