Find stored procedure that references multiple specific tables

Question

This is the SQL that I am using:

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 = 'PacManPackage' and o.name = 'PacManCompanyPackage' order by sp.Name

If I remove the o.name and view with only one table it works. But I need to find stored procedures that reference a few different tables (but they must reference all of them)

How can "o.name 'PacManPackage' and o.name = 'PacManCompanyPackage'" both be true? — Bill Gregg
– Bill Gregg, Commented Feb 3, 2015 at 16:03

Rhys Jones · Accepted Answer · 2015-02-03 16:12:26Z

If I've understood you correctly, you want to find stored procedures or scalar functions that reference all of a set of tables? How about this for a quick and dirty answer;

select sp.name from sys.objects sp inner join sys.sql_expression_dependencies sed on sed.referencing_id = sp.object_id inner join sys.objects t on t.object_id = sed.referenced_id and t.type = 'U' where sp.type in ('P', 'FN') and t.name in ('PacManPackage', 'PacManCompanyPackage') group by sp.name having count(*) = 2 -- change accordingly

Collectives™ on Stack Overflow

Find stored procedure that references multiple specific tables

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related