5

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?

2
  • 5
  • @AaronBertrand Thanks for the link. To summarize, it sounds like INFORMATION_SCHEMA is an obsolete and incomplete list of sql objects left over from the SQL 2000 days, and you'd be better off using the newer replacements such as catalog views. From the way you and gbn referenced it, I was expecting something truly horrible to happen if I kept using it :)
    – Rachel
    May 8, 2013 at 15:22

4 Answers 4

6

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).

7

Use one of

Never use

  • INFORMATION_SCHEMA
  • sys.syscomments
3
  • Thank you, it looks like sys.sql_modules will work for me. Can you expand on why you should never use INFORMATION_SCHEMA?
    – Rachel
    May 8, 2013 at 15:05
  • @Rachel: If your search term spans a 4000 (or 8000 or 12000) boundarym it can never be found. Unless you want to CONCAT all the rows into one
    – gbn
    May 8, 2013 at 15:24
  • @gbn Thanks, your answer, combined with Aaron's comment, made me think that there was some reason I should never use INFORMATION_SCHEMA for anything, but I see now you were only referring to the context of my question of searching procedure text :)
    – Rachel
    May 8, 2013 at 15:30
-1
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

-3
SELECT name 
FROM sys.all_objects 
WHERE  Object_definition(object_id) LIKE '%text%' 
ORDER BY name
1
  • You should typically try to explain how your answer works and how it solves the problem asked in the question.
    – Hannah Vernon
    Apr 22, 2016 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.