How to Query SQL Server System Tables for Text in Stored Procedures

It is often very useful to be able to query the SQL Server system tables to find data about stored procedures. Rather than running visual searches through the GUI or scripting the stored procedures, the fastest way to find what you are looking for is a direct query via SSMS (SQL Server Management Studio).

Specifically, I sometimes want to get a list of stored procedures in a database that contain a specific line of text that I want to update. An example would be that I want to look for all stored procedures that have a ‘like’ where condition and I want to change this to an ‘=’ condition. Searching through hundreds of procedures through the GUI or scripting all of the procedures and then running a search through the text are not viable options, so a call to the system tables is definitely in order.

SQL Server 2000, 2005, and 2008 all support queries against the creation date of the procedure, the name of the procedure, and the content of the procedure. However it is important to keep in mind that the system tables of SQL Server 2000 are different from those of SQL Server 2005 and 2008, so different system tables need to be queried.

How to Query SQL Server 2005 and 2008

The system tables in SQL Server 2005 & 2008 that we will need to query are sys.objects and sys.sql_modules. Sys.objects contains general information such as the name of the stored procedure, the type of object (in this case we are looking for type=’P’ which is for stored procedures), the creation date and the last modification date of the stored procedure. Sys.sql_modules is then joined in via the object_id and we can get the text of the stored procedures listed using the definition column.

The optional filters we can use to narrow down the stored procedures we are looking for are:

1. type=’P’ to select only Stored Procedures
2. create_date to restrict by the creation date of the stored procedures.
3. modify_date to restrict by the last modified date of the stored procedures (Note that this is a newly added field and cannot be queried in SQL Server 2000).
4. Name like ‘spName%’ which you can use to restrict the results by all or part of the names of the stored procedures
5. Definition like ‘%text to search for%’ which is the filter you will use to search for stored procedures containing a specific line of text.

Here is an example of a query against an SQL Server 2005 or 2008 database for stored procedures with names starting with spName and containing a WHERE clause condition of FirstName LIKE @MyName + ‘%’

SELECT definition, name
FROM sys.objects
INNER JOIN sys.sql_modules ON sys.objects.object_id = sys.sql_modules.object_id
WHERE type='P'
AND name like 'spName%'
AND definition LIKE '%FirstName LIKE @MyName + ''%''%'
ORDER BY sys.objects.name

Alternate Queries for SQL Server 2005, 2008

The Information_Schema View

Alternately you can run your query against the Information_Schema table. Microsoft claims that this abstraction from querying the system tables exists so that if they change the system tables in future versions of SQL Server, we can continue using the INFORMATION_SCHEMA views without needing to update any code. This makes sense and would be useful if the ROUTINE_DEFINITION text would return the full stored procedure text. Instead the ROUTINE_DEFINITION text seems to return the first 4000 characters or so, which renders this method of querying relatively useless when compared with the system tables.

Here is an example query searching for stored procedure name and text where the text contains a keyword:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_TYPE='PROCEDURE'
AND ROUTINE_DEFINITION LIKE '%TextToSearchFor%'

The Deprecated Syscomments table

If you aren’t as worried about searching through just stored procedures, then you can query the older syscomments system table.  Note however that Microsoft is deprecating the syscomments table in favor of the sys.sql_modules table. In the example for SQL Server 2000 below you can see an example query using syscomments that also works for SQL Server 2005 and SQL Server 2008.

How to Query SQL Server 2000 for Stored Procedures Containing Text

For SQL Server 2000, the system tables to query are sysobjects, which will contain the name and create date of your procedures, and syscomments, which will contain the content of your stored procedures.

Further optional filters are:

1. For type=’P’ which is for stored procedures
2. For crdate, which is for the date when the stored procedure was first created
3. Name like ‘spName%’ which you can use to restrict the results by all or part of the names of the stored procedures
4. Text like ‘%text to search for%’ which is the filter you will use to search for stored procedures containing a specific line of text.

Here is an example of a query against an SQL Server 2000 database for stored procedures with names starting with spName and containing a WHERE clause condition of FirstName LIKE @MyName + ‘%’

SELECT text,name
FROM sysobjects
INNER JOIN syscomments ON sysobjects.id = syscomments.id
WHERE type = 'P'
AND name like 'spName%'
AND text LIKE '%FirstName LIKE @MyName + ''%''%'
ORDER BY name