Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to Find Keywords in SQL Server Stored Procedures and Functions


By:   |   Last Updated: 2019-05-03   |   Comments (5)   |   Related Tips: More > Dynamic Management Views 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.

query results

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


Last Updated: 2019-05-03


get scripts

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, May 02, 2019 - 7:21:29 PM - David Gillett Back To Top

 This method doesn't search the entire text of long stored procedures.  This code will:

DECLARE @ObjectType VARCHAR(25)= 'FN'; -- TR, FN, IF, V, P
DECLARE @Code VARCHAR(25)= 'KMEP';
SELECT 
     s.name + '.' + o.name
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;

Monday, April 23, 2018 - 11:50:36 AM - SASI Kumar Back To Top

How to get all related SP's from entire Server( from all the databases) based on given string?

 

Thanks in advance,

Sasi Kumar


Thursday, April 23, 2015 - 4:09:28 AM - Anders Norell Back To Top

Great tip! It sure helped me

Thanks Anders


Thursday, April 05, 2012 - 5:38:25 AM - Griffster Back To Top

I've noticed that if your stored procedure contains more than 4000 characters and the word you're searching for is after the 4000th character, this select statement will not find it.


Tuesday, December 21, 2010 - 6:20:21 AM - lacoderr Back To Top

Another method to search in stored procedures, triggers and functions:

http://it.expertmonster.com/question/How-to-search-in-stored-procedures-108.html


Learn more about SQL Server tools