Text search in Stored Procedures in SQL Server 2005 and 2008

In this ms sql server tutorial we will discuss Text search in Stored Procedures in SQL Server 2005 and 2008. There are many ways to accomplish this task. Lets have a look on those methods.

Method 1:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%FOO%' 
AND ROUTINE_TYPE='PROCEDURE'

Method 2:

SELECT OBJECT_NAME(ID) 
FROM SYSCOMMENTS 
WHERE [TEXT] LIKE '%FOO%' 
AND OBJECTPROPERTY(ID, 'ISPROCEDURE') = 1 
GROUP BY OBJECT_NAME(ID)

Method 3:

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%FOO%' 

Remember routine_definition is cropped at 4000 chars if you have a long stored procedure. sys.sql_modules doesn't have such restriction.

So that's it. Hopefully you will find this tutorial very handy.

Read more...