Nested Stored Procedures - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Nested Stored Procedures

Author

Topic

raky
Aged Yak Warrior

767 Posts

Posted - 2010-03-20 : 01:20:49

Is there any t-sql query to find the nested stored procedures( Procedures which are calling another stored procedures in their body) in a database?

Please help..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-20 : 01:37:01

nope..you've to manually check using
http://msdn.microsoft.com/en-us/library/ms174402(SQL.90).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-20 : 02:11:00

Yes why not ?

sp_depends <procedure name>

will give you all the objects which are being used in the procedures

Vaibhav T

raky
Aged Yak Warrior

767 Posts

Posted - 2010-03-20 : 02:33:56

Thanx for ur replies.yes,i know it. But i dont want to do manual check on all stored procedures...need some sql query to do that.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-20 : 03:05:48

Try this


SELECT objectname [Proc name], d.name [Nested Proc] FROM
(
SELECT o.object_id, o.name objectname, d.depid FROM sysdepends d
join sys.objects o on d.id = o.object_id and Type = 'p'
where readobj = 0
) o LEFT JOIN sys.objects d on d.object_id = o.depid and type = 'p'
WHERE d.name is not null
ORDER BY objectname

Vaibhav T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources