The two highest voted answers use a lot of deprecated tables that should be avoided.
Here’s a much cleaner way to do it.
Get all the tables on which a stored procedure depends:
SELECT DISTINCT p.name AS proc_name, t.name AS table_name
FROM sys.sql_dependencies d
INNER JOIN sys.procedures p ON p.object_id = d.object_id
INNER JOIN sys.tables t ON t.object_id = d.referenced_major_id
ORDER BY proc_name, table_name
Works with MS SQL SERVER 2005+
List of Changes:
sysdepends
should be replaced withsys.sql_dependencies
- The new table uses
object_id
instead ofid
- The new table uses
referenced_major_id
instead ofdepid
- The new table uses
- Using
sysobjects
should be replaced with more focused system catalog views- As marc_s pointed out, instead use
sys.tables
andsys.procedures
- Note: This prevents having to check where
o.xtype="p"
(etc.)
- As marc_s pointed out, instead use
-
Also, there is really no need for a CTE which uses
ROW_NUMBER()
just in order to make sure we only have one of each record set returned. That’s whatDISTINCT
is there for!- In fact, SQL is smart enough to use DISTINCT behind the scenes.
-
I submit into evidence: Exhibit A. The following queries have the same Execution Plan!
-- Complex WITH MyPeople AS ( SELECT id, name, ROW_NUMBER() OVER(PARTITION BY id, name ORDER BY id, name) AS row FROM People) SELECT id, name FROM MyPeople WHERE row = 1 -- Better SELECT DISTINCT id, name FROM People