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
~ Asked on 2011-09-01 14:54:53
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
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