sqlt-sqlstored-proceduressql-server-2005

How to identify all stored procedures referring a particular table


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


Solution

  • SELECT Name
    FROM sys.procedures
    WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TableNameOrWhatever%'
    

    BTW -- here is a handy resource for this type of question: Querying the SQL Server System Catalog FAQ