Posts

Showing posts from May, 2017

To get a list of tables used in a specific set of stored procedures

SELECTDISTINCT SP.name AS'Procedure_Name', Tbl.name AS'Table_Name' FROMsys.sysdependsAS Dep INNERJOIN sys.sysobjectsAS SP ON Dep.id = SP.id INNERJOIN sys.sysobjectsAS Tbl ON Dep.depid = Tbl.id WHERE (Dep.depnumber = 1)AND -- To filter with stored procedure name, here all sps starts with CSP_Outsorce (SP.name LIKE'CSP_Outsorce%')AND -- To filter with table name, checking whether sps are accessing tables starts with 'PAYROLL' (Tbl.name LIKE'PAYROLL_%') ORDERBY'Table_Name','Procedure_Name'