SQL 2005 - Search stored procedures for text (Not all text is being searched)

Question

The following bits of code do not seem to be searching the entire routine definition.

Code block 1:

select top 50 * from information_schema.routines
where routine_definition like '%09/01/2008%' and specific_Name like '%NET'

Code Block 2:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%EffectiveDate%' AND ROUTINE_TYPE='PROCEDURE' and ROUTINE_NAME like '%NET'

I know for a fact that these bits of SQL work under most circumstances. The problem is this: When I run this for "EffectiveDate" which is buried at line ~800 in a few stored procedures, these stored procedures never show up in the results. It's as if "like" only searches so deep.

Any tips on fixing this?

I want to search the ENTIRE stored procedure for the specified text.

Thanks!

Answer 1

select * 
FROM   INFORMATION_SCHEMA.ROUTINES 
WHERE  OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped') =0 
       and OBJECT_DEFINITION(OBJECT_ID(SPECIFIC_NAME)) like '%EffectiveDate%' 
       AND ROUTINE_TYPE='PROCEDURE'
       AND ROUTINE_NAME like '%NET'

Use the object definition instead.

Answer 2

Just to clarify why the procedure text is truncated:

ROUTINE_DEFINITION nvarchar(4000)

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 that you obtain the complete definition, query the OBJECT_DEFINITION function or the definition column in the sys.sql_modules catalog view.