How to identify all stored procedures referring a particular table

Question

I created a table on the development environment for testing purpose and there are few sp's which are referring to this table. Now I have had to drop this table as well as identify all sp's which are referring to this table. I am facing difficulty to find a list of all sp's. Please suggest some queries by assuming that the table name is 'x' and the database is SQL server 2005.

Answer 1

Use the below query:

SELECT Name

FROM sys.procedures

WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TableNameOrWhatever%'

For more information, you can refer to: Querying the SQL Server System Catalog FAQ