How to identify all stored procedures referring a particular table

147

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.

This question is tagged with sql tsql sql-server-2005

~ Asked on 2011-09-01 14:54:53

The Best Answer is


282

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

~ Answered on 2011-09-01 15:07:40


31

The following works on SQL2008 and above. Provides a list of both stored procedures and functions.

select distinct [Table Name] = o.Name, [Found In] = sp.Name, sp.type_desc
  from sys.objects o inner join sys.sql_expression_dependencies  sd on o.object_id = sd.referenced_id
                inner join sys.objects sp on sd.referencing_id = sp.object_id
                    and sp.type in ('P', 'FN')
  where o.name = 'YourTableName'
  order by sp.Name

~ Answered on 2013-08-07 15:10:06


Most Viewed Questions: