SQL SERVER - How to serach stored procedure for a specific text

Search in stored procedures can be performed through INFORMATION_SCHEMA.ROUTINES view, or syscomments. Let say if you want to search for keyword 'power' in your stored procedure. try following:
1. Search using INFORMATION_SCHEMA.ROUTINES view


SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%power%' AND ROUTINE_TYPE='PROCEDURE'
2. Search using syscomments


SELECT OBJECT_NAME(id) FROM syscomments WHERE [text] LIKE '%power%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id)
BUT you may not get correct result if your stored procedure is very large(>8kb) since whole text of procedure is not being returned. You will not see this problem in sql2005 as in sql2005 there are function like OBJECT_DEFINITION , which returns the whole text of the procedure.


SELECT Name FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE '%power%'

SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE Definition LIKE '%power%' AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%powerr%' AND ROUTINE_TYPE = 'PROCEDURE'


Still its not 100% perfect. There are 3rd party tools available to do this i.e.Gplex Database (www.gplexdb.com)