How can I search the full text of a stored procedure for a value?

Question

I use the following script to search the text of all stored procedures when I want to find specific values.

SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE @searchText
ORDER BY ROUTINE_NAME   

I recently discovered that ROUTINE_DEFINITION stops after 4000 characters, so some procedures were not getting returned when they should have been.

How can I query the full text of a stored procedure for a value?

Answer 1

From BOL, on INFORMATION_SCHEMA.ROUTINES:

Column name: ROUTINE_DEFINITION
Data type: nvarchar(4000)
Description: Returns the first 4000 characters of the definition text of the function or stored procedure if the function or stored procedure is not encrypted. Otherwise, returns NULL. To ensure you obtain the complete definition, query the OBJECT_DEFINITION function or the definition column in the sys.sql_modules catalog view.

You can therefore view the rest by changing you script as follows:

SELECT 
    ROUTINE_NAME, 
    ROUTINE_TYPE, 
    ROUTINE_DEFINITION as First4000, 
    OBJECT_DEFINITION(object_id(ROUTINE_NAME)) as FullDefinition
FROM 
    INFORMATION_SCHEMA.ROUTINES
WHERE 
    OBJECT_DEFINITION(object_id(ROUTINE_NAME)) LIKE @searchText
ORDER BY 
    ROUTINE_NAME

The column aliased above as FullDefinition will return the rest of the content as nvarchar(max).

Answer 2

Use one of

• sys.sql_modules
• Red Gate SQL Search (free!)
• OBJECT_DEFINITION

Never use

• INFORMATION_SCHEMA
• sys.syscomments

Answer 3

SELECT name
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE ‘%strHell%’ 

For more details see my blog post: How to Search a text in Stored Procedure, Trigger, Function or View

Answer 4

SELECT name 
FROM sys.all_objects 
WHERE  Object_definition(object_id) LIKE '%text%' 
ORDER BY name