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:
  • Dynamic Management Views and Functions – SQL Server 2005 Exposed
  • SQL Server 2000 to 2005 Crosswalk – Code Identification
  • SQL Server 2000 to 2005 Crosswalk – Locking Resources
  • Finding process percentage complete with dynamic management views
  • SQL Server 2005 Resource Database Values in Dynamic Management Views\Functions

Tim Ford

Tim Ford is a Senior Database Administrator with MindBody in San Luis Obispo, California and is in the process of relocating west to the Pacific Northwest from Michigan. Since 2010 he’s produced Microsoft Data Platform training events branded as SQL Cruise from Alaska to the Caribbean and the Mediterranean at Tech Outbound, an events company specializing in technical training in unconventional locations. His SQL Cruise events take place on cruise ships in the Caribbean, Alaska, and the Mediterranean. Tim also is the Executive VP of Marketing for PASS, the global association for Microsoft data professionals. He also is a contributing author for itprotoday. Tim loves helping people find their true potential through education and building networks between Thought Leaders in various fields and those who are just starting on their careers or struggling to find their footing in established careers. If you’re looking for this sort of experience then check out the next SQL Cruise event taking place this August in Seattle.

• MSSQLTips Awards: Acheiver (75+ tips) – 2010