Jon Milliken Jon Milliken - 1 month ago 7
SQL Question

Query to list which tables are used in stored procedures

I'm looking for a way to see all what stored procedures impact specific tables. Is there a better way than using

LIKE
?

select * from DatabaseName.information_schema.routines
where routine_type = 'PROCEDURE'
and ROUTINE_DEFINITION like '%TableName%'


source: Query to list all stored procedures

Answer

This query will give you all the Stored Procedures that depend on table 'MyTable':

SELECT s.name as [ObjectName], t.name as [DependsOn]  
  FROM sys.sql_expression_dependencies d
       INNER JOIN sys.objects s ON d.referencing_id = s.object_id
       INNER JOIN sys.objects t ON d.referenced_id = t.object_id
 WHERE t.name = 'MyTable' AND s.type = 'P'

You can alter the WHERE clause to be more specific, or less specific, or also add in functions, views, etc. if you wish.

Comments