Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
4  
    
@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 '13 at 15:22
1  
@Rachel: INFORMATION_SCHEMA was designed by committee. And it is the Java of RDBMS metadata: uncyclopedia.wikia.com/wiki/Java (first quote. NSFW) –  gbn May 8 '13 at 15:27
1  
@gbn Dude, you just caused me to lose an hour trawling uncyclopedia. I am grateful! –  RoKa May 8 '13 at 16:21

3 Answers 3

up vote 3 down vote accepted

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

share|improve this answer

Use one of

Never use

  • INFORMATION_SCHEMA
  • sys.syscomments
share|improve this answer
    
... beat me to it.... free up-vote for you! –  RoKa May 8 '13 at 15:01
    
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 '13 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 '13 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 '13 at 15:30

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

For more details: http://www.zilckh.com/how-to-search-a-text-in-stored-procedure-trigger-function-or-view/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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