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'

Comments

Popular posts from this blog

മലയാളത്തില്‍ മൊത്തം എത്ര അക്ഷരങ്ങള്‍ ഉണ്ട്?

Convert Number To Words in SQL Sever

Crystal reports load report failed: Could not load file or assembly CrystalDecisions.Web, Version=10.2.3600.0