Search Through Code on Multiple Databases

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.

Listing 1: Traditional T-SQL Statement That Looks for a Word in Code
  
USE ADVENTUREWORKS;
DECLARE @SEARCH_STRING VARCHAR(50) 
SET @SEARCH_STRING = 'CheckDate'

SELECT
  sys.schemas.name AS \[Schema Name\],
  sys.objects.name AS \[Object Name\],
  type_desc AS \[Object Type\],
  substring(definition,CHARINDEX(@SEARCH_STRING,definition)-30, 60)
    AS \[Text Context\]
FROM sys.objects
JOIN sys.sql_modules
  ON sys.objects.object_id = sys.sql_modules.object_id
JOIN sys.schemas
  ON sys.objects.schema_id = sys.schemas.schema_id
WHERE CHARINDEX(@SEARCH_STRING,sys.sql_modules.definition) > 0


 

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 
  'print ''The current db is ?'''

results in the following being displayed:

The current db is master
The current db is tempdb
The current db is model
The current db is msdb
The current db is AdventureWorksDW
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.

Listing 2: SearchThroughAllCode.sql


CREATE PROCEDURE \[dbo\].\[SearchThroughAllCode\]
  @searchStringIn varchar(255) AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @SQL VARCHAR(2000)
-- BEGIN CALLOUT A
  SET @SQL = 'USE ?; 
  SELECT ''?'' AS DATABASE_NAME, 
-- END CALLOUT A
    sys.schemas.name AS \[Schema Name\],
    sys.objects.name AS \[Object Name\] ,
    type_desc AS \[Object Type\],
    substring(definition,CHARINDEX(''' + @SEARCHSTRINGIN + ''',
      definition)-30, 60)
    AS \[Text Context\]
  FROM sys.objects
  JOIN sys.sql_modules
    ON sys.objects.object_id = sys.sql_modules.object_id
  JOIN sys.schemas
    ON sys.objects.schema_id = sys.schemas.schema_id
  WHERE CHARINDEX(''' + @SEARCHSTRINGIN + ''',definition)>0'

  CREATE TABLE #SEARCH_RESULTS
   ( DATABASE_NAME NVARCHAR(128),
    \[SCHEMA_NAME\] VARCHAR(128),
    \[OBJECT_NAME\] VARCHAR(128),
    \[OBJECT_TYPE\] VARCHAR(20),
    TEXT_CONTEXT VARCHAR(60) )
  INSERT INTO #SEARCH_RESULTS EXEC sp_MSforeachdb @SQL

  SELECT * FROM #SEARCH_RESULTS
  ORDER BY
  DATABASE_NAME, \[SCHEMA_NAME\], \[OBJECT_NAME\]
END


 

The syntax for the SearchThroughAllCode stored procedure is

EXECUTE \[dbo\].\[SearchThroughAllCode\]
  '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\]
  'CheckDate'

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\]
  'SearchString'

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.