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)
Subscribe to:
Posts (Atom)
