Finding all the tables refered in a Stored Proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Finding all the tables refered in a Stored Proc

Author

Topic

shamils
Starting Member

1 Post

Posted - 2010-06-28 : 00:19:28

hi
there are about 30+ stored procedures in my DB and i want to find all the tables referred (even if its a SELECT statement) in each stored Procedure.
while browsing the internet i found out this code but it seems only returning a given list of procedures.

/*Get list of tables used in a stored procedure
By Deepthi Viswanathan Nair, 2007/08/30
URL: http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31896/ */
SELECT DISTINCT o.id, o.name as 'Procedure_Name' , oo.name as 'Table_Name', d.depid, d.depnumber
FROM sysdepends d, sysobjects o, sysobjects oo
WHERE o.id=d.id and o.name= 'SampleSP' -- Stored Procedure Name
and oo.id=d.depid and depnumber=1
ORDER BY o.name,oo.name

i am thinking something like this. first of all scan and get the names of all the tables. and then scan one by one the contents of each SP.

please suggest any solutions.
Regards

Shamil Saleem.

Sachin.Nand

2937 Posts

Posted - 2010-06-28 : 03:15:04

This query does return all the table names of all dependent tables in a SP.


SELECT DISTINCT o.id, o.name as 'Procedure_Name' , oo.name as 'Table_Name'
FROM sysdepends d, sysobjects o, sysobjects oo
WHERE o.id=d.id 
and oo.id=d.depid and depnumber=1
ORDER BY o.name,oo.name

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources