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.
- The first is that SQL Server 2000 does not understand the variable type VARCHAR(MAX).
- The second is that you cannot use an EXECUTE statement to insert into a table variable.
- The third is that you will need to adjust this query to handle the old system table views.