How to Find Keywords in SQL Server Stored Procedures and Functions

Problem

How many times have you had to troubleshoot or make changes to an existing database that is not documented properly or completely? Even need to look for a specific stored procedure that references a specific table or process? If you have intimate knowledge of the database then this may not be much of an issue for you. What happens if this database is from an external developer, a turn-key solution provider, or developed by another individual within your company? If you utilize the INFORMATION_SCHEMA.ROUTINES view to research and troubleshoot this issue, the task goes from daunting to a cake-walk.

Solution

The INFORMATION_SCHEMA.ROUTINES view, introduced in SQL 2000 exposes metadata in the current database as it pertains to content derived from the syscomments and sysobjects system views and system tables. It contains one row for each function and stored procedure in the database for which the current user has rights. This is an important note to make. If you have limited rights to the database, INFORMATION_SCHEMA.ROUTINES may not return a complete set of results as it pertains to the query you are executing.

If you are searching for a specific keyword or block of text you can simply query the INFORMATION_SCHEMA.ROUTINES view as follows, substituting your search string for “KEYWORD”:

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

This is the equivalent of running the following query directly against the system tables/views:

SELECT sys.sysobjects.name, sys.syscomments.text
FROM sys.sysobjects INNER JOIN syscomments 
ON sys.sysobjects.id = sys.syscomments.id
WHERE sys.syscomments.text LIKE '%KEYWORD%' 
AND sys.sysobjects.type = 'P'
ORDER BY sys.sysobjects.NAME

The INFORMATION_SCHEMA.ROUTINES view can be used to search for content of functions as well. Simply alter your WHERE clause as follows, substituting your search string in place of “KEYWORD” once again:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%KEYWORD%' 
AND ROUTINE_TYPE='FUNCTION'
ORDER BY ROUTINE_NAME

To highlight the fact that this process is backward-compatible to Microsoft SQL Server 2000, I’ve run the following query against the Northwind database. I’m interested in finding all the stored procedures that utilize the ROUND() function:

USE Northwind
GO
SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%ROUND%' 
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY ROUTINE_NAME
GO

From this I am able to determine that there are two stored procedures utilizing that function.

Here is another example to find code that contains a string. This is an example provided by one of our readers. With this code you can specify the type of object you want to search:

TR – trigger
FN – scalar function
IF – table valued function
V – view
P – procedure

DECLARE @ObjectType VARCHAR(25)= 'P'; -- TR, FN, IF, V, P
DECLARE @Code VARCHAR(25)= 'customer';
SELECT 
     s.name + '.' + o.name, m.definition
FROM
   sys.sql_modules AS m
   INNER JOIN sys.objects AS o ON m.object_id = o.object_id
   INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE o.type = @ObjectType
      AND o.name NOT LIKE 'sp_%'
      AND m.definition LIKE '%' + @Code + '%'
ORDER BY
     s.name
   , o.name;

Next Steps

The next time you need to troubleshoot an object, consider exploring the INFORMATION_SCHEMA views. We’ve been told since SQL Server 2000 not to drill into the system tables, yet we all do – we are comfortable with doing so after eight years. Do not let fear of the unknown stop you from simplifying your metadata querying by making use of the INFORMATION_SCHEMA views, Dynamic Management Views and Dynamic Management Functions.
Stay tuned for more INFORMATION_SCHEMA, DMV, and DMF tips. Until then, check out these tips:

Resources

Community

Subscribe