List All Tables in a Stored Procedure

Posted: November 22, 2011 in SQL Server 2005

To get a list of all tables referenced in a stored procedure, you can try the following:

-- Declare variables
DECLARE @lv_procedure_name VARCHAR(200);

-- Initialise variables
SET @lv_procedure_name = 'YourProcedureName';

SELECT   DISTINCT o.id, 
                  o.name 'procedure_name', 
                  oo.name 'table_name', 
                  d.depid, 
                  d.depnumber 
FROM     sysdepends            d 
         INNER JOIN sysobjects o   ON o.id   = d.id
                                  AND o.name = @lv_procedure_name
         INNER JOIN sysobjects oo  ON oo.id  = d.depid 
WHERE    d.depnumber = 1
ORDER BY o.name,
         oo.name;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s