September 25, 2009 by Mike Hillwig
SQL Server gives us a pretty good GUI for most things. Every once in a while, though, it’s pretty rewarding to dig into the system tables to get this information the old fashioned way.
I was working on a problem with a stored procedure on a SQL 2000 instance. This procedure called procedures and functions that called procedures and functions about six layers deep. The GUI wasn’t quite helping me find the data I wanted, so I did it myself.
By joining sysdepends to sysobjects twice, I got everything I needed. One instance of sysobjects is for the name of the object we’re investigating. The other instance is for all of the objects that the first object depends on.
The query itself is pretty simple. Notice that I’m also pulling the type of the object as well. This can come in handy. Then I filtered it to only show me the procedures and functions.
select distinct obj.name, dep.name, dep.xtype
from sysdepends sd
inner join sysobjects obj
on sd.id = obj.id
inner join sysobjects dep
on sd.depid = dep.id
where obj.name = ‘procname’
and dep.xtype IN (‘FN’, ‘P’)
This gave me exactly what I was looking for. But let me take it one more step.
I did find one caveat to this process. If any of these dependent stored procedures call other stored procedures, I’ll need to repeat the process with those stored procedures. In our current ERP system, we have nested stored procedures like you wouldn’t believe.