Skip to main content

Posts

Showing posts from May, 2017

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

SELECT DISTINCT SP . name AS 'Procedure_Name' , Tbl . name AS 'Table_Name' FROM sys . sysdepends AS Dep INNER JOIN      sys . sysobjects AS SP ON Dep . id = SP . id INNER JOIN      sys . sysobjects AS 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_%' )   ORDER BY 'Table_Name' , 'Procedure_Name'