Information_Schema.Routines doesn't return all procedure names

How to get a list of Procedures containing Specific Text / How to get a list of Procedures using Particular Table



Here is one method we generally use for returning Stored Procedures containing a particular text

SELECT ROUTINE_NAME



FROM INFORMATION_SCHEMA.ROUTINES


WHERE ROUTINE_DEFINITION LIKE '%used_by_id%'
AND ROUTINE_TYPE = 'PROCEDURE'


order by ROUTINE_NAME

 
However, we found it has some problems when the text appears at the fag end of big procedures. Instead try the following also and match the result
 
SELECT OBJECT_NAME(id)




FROM syscomments



WHERE [text] LIKE '%used_by_id%'



AND OBJECTPROPERTY(id, 'IsProcedure') = 1



GROUP BY OBJECT_NAME(id)







SELECT Name



FROM sys.procedures



WHERE OBJECT_DEFINITION(object_id) LIKE '%used_by_id%'



order by name







SELECT OBJECT_NAME(object_id)



FROM sys.sql_modules



WHERE Definition LIKE '%used_by_id%'



AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1



order by OBJECT_NAME(object_id)