Search text/string|Find keyword in Sql Server Function and Procedure

   Tweet

To find a text or string or keyword across all function and procedure in sql server 2005 using INFORMATION_SCHEMA.ROUTINES & SYS.SQL_MODULES
Option1:

select * from INFORMATION_SCHEMA.ROUTINES

Provides all the details of user-defined Function and Procedure in current database.
By using "ROUTINE_DEFINITION" column in "INFORMATION_SCHEMA.ROUTINES" we can search for any text in all function and procedure
Sample to get List of stored procedures and function that used particular table. i.e to search for table name "testtable" in all function and procedure
we can do

select ROUTINE_NAME, ROUTINE_DEFINITION from 
INFORMATION_SCHEMA.ROUTINES where ROUTINE_DEFINITION like '%testtable%'

Option2 (Recommended):
To search for table name "testtable" using sys.sql_modules

select * from sys.sql_modules where definition like '%test%'

Option 2 would most recommended as INFORMATION_SCHEMA.ROUTINES has only first 8000 characters of sql code but sys.sql_modules stores full code.

   Tweet