Friday, September 28, 2012

SQL to get the names of the tables referenced in stored procedures

below is a simple sql to retrieve a list of all the stored procedures and the tables referenced in those procedures:

SELECT DISTINCT a.name AS [Procedure Name], b.name AS [Table Name]
FROM sysobjects as a
INNER JOIN sysdepends d ON a.id=d.id
INNER JOIN sysobjects b ON d.depid=b.id
WHERE a.xtype = 'P'
ORDER BY a.name, b.name

No comments:

Post a Comment