Search Through SQL Server Stored Procedures in all Databases for a Line of Text

In an earlier article I explained how to search a SQL server database for Stored Procedures containing some text. This is certainly useful to be able to do, but how about searching all databases on your database server?

Being able to create a catalogue of Stored Procedures in all databases on your database server containing a line of text is definitely a useful way to get information globally. For example you could search for all stored procedures that read or write from a particular table. If you are just getting familiar with a new project or system then the ability to get data quickly and completely will save you a lot of time searching. And believe me when I say that just scripting all stored procedures and then doing a text-based search for the line of text is not a viable alternative.

So how is it done? How does one search all databases on a server for all stored procedures containing a key text value?

Some Background on How it’s Done

The first step is to create a cursor that will loop through all databases on your database server. You can do so by returning the name column from the master.sys.databases meta table.

Then once your cursor to loop through all non-system databases on your database server is functional, you will need to query the Stored Procedures in each database for only the ones that contain a particular line of text. As you are generating your list in a cursor, it’s best to add your results to a temporary table that you can query later for the full list of databases and procedures in each database.

One point to note in this process is that when you are searching each stored procedure for text, you should query the system table sys.sql_modules directly. If you query the INFORMATION_SCHEMA.ROUTINES view instead, then you will not be searching through the full text of your stored procedures.

Example Query to Search for Text in all Stored Procedures on your Database Server

Please note that this syntax is specific to SQL Server 2005 / 2008 and that you will need a different query to get this information in SQL Server 2000.

Here is the example query. In this case we are looking for all stored procedures on our database server that contain the text TextToSearchFor. Also note that we are intentionally excluding the system databases: master, model, tempdb, and msdb.

DECLARE @DBName VARCHAR(100)
DECLARE @ResultsTable TABLE(
 DBName VARCHAR(100),
 SPName VARCHAR(255),
 SPText VARCHAR(MAX)
 )
DECLARE MY_CURSOR Cursor
FOR 
SELECT masterDb.Name
FROM master.sys.databases masterDb
WHERE masterDb.Name NOT IN ('master','model','tempdb','msdb')
ORDER BY masterDb.Name
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @ResultsTable (DBName,SPName,SPText)
EXEC('
SELECT ''' + @DBName + ''' [dbName],tblObjects.name,tblModules.definition
FROM ' + @DBName + '.sys.objects tblObjects
INNER JOIN ' + @DBName + '.sys.sql_modules tblModules ON tblObjects.object_id = tblModules.object_id
WHERE type=''P''
AND definition LIKE ''%TextToSearchFor%''
ORDER BY tblObjects.name
')
FETCH NEXT FROM MY_CURSOR INTO @DBName
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
SELECT DBName,SPName,SPText FROM @ResultsTable
ORDER BY DBName,SPName

Notes about SQL Server 2000

If you want to adapt this query to SQL Server 2000 then you will need to change three main parts of this query.

1. The first is that SQL Server 2000 does not understand the variable type VARCHAR(MAX).
2. The second is that you cannot use an EXECUTE statement to insert into a table variable.
3. The third is that you will need to adjust this query to handle the old system table views.