query to find out Tables, Functions, Procs in SP

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 2000 Forums

Transact-SQL (2000)

query to find out Tables, Functions, Procs in SP

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-06-07 : 11:18:40

How do I get the list of all tables and funtions and procs being used in a SP and if possible nested funtions and tables and procs as well. Like Proc A uses funtion ABC, and Table XYZ.
Funtion ABC uses Funtion ABC1 and TABLE XYZ1 so I should be able to get that in my list of result and if possible the nesting level.

Any help will be appreciated.

http://www.sqlservercentral.com/columnists/rVasant/queryingsystemtables.asp

does not tells about my query. And this query gives LIST OF FUNCTIONS VIEWS AND STORED PROCS A TABLES IS BEING USED IN
But not vice versa.

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so on sc.id=so.id
WHERE sc.text like '%GL1PositionTL%'

Ashley Rhodes

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-06-08 : 06:18:47

Hi,

u can get those from sysdepends table.

For ex the following ll list all the tables and sps associated with them in a database
SELECT DISTINCT SO.[Name] AS 'ProcedureName', SODP.[Name] AS 'TableName'
FROM SysObjects SO
LEFT JOIN SysDepends SD ON SO.[Id] = SD.[Id]
LEFT JOIN SysObjects SODP ON SODP.[Id] = SD.DepId
WHERE SO.Xtype = 'P'
AND SO.[Name] NOT LIKE 'dt_%'
ORDER BY 1, 2

like wise u can get the functions etc

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-08 : 12:05:09

Just an FYI that sydepends is not very reliable..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-06-11 : 09:05:34

i get table names a NULL. so i guess sysdepends is not reliable.
but when I use it for sample databases I get the correct results.

Hmmm don't know why.

Ashley Rhodes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources