Using sysdepends the old fashioned way

6

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.

6 thoughts on “Using sysdepends the old fashioned way

  1. Manzano says:

    Dear Amy,我来你家看你了. Are u moved!!!Regarding your suggestion on going out, I think these days are prttey cold, and in-door activity is preferred. But, long time no see your wonderful picture拉.

  2. Superb information here, ol’e chap; keep burning the midnight oil.

  3. Zdravím…mam 100l dvouplášťový el. kotel.(12kw)V meziplášti mam cca 14 litru vody. PÅ™i vaÅ™ení mi po zahřátí neustále utíká ( hodnÄ› utíká !! ) pára z pÅ™etlakového ventilu, který je na meziplášti . Je to normální?Co tam místo vody dát transformátorový olej? Ten by se vypaÅ™ovat nemÄ›l a nic by z pÅ™etlakového ventilu jít nemÄ›lo. nebo se platu?.. ( pÅ™etlakoví ventil je nastavitelný – na jaký tlak ho nastavit?)+ Proč nemůže vodní dvouplášt destilovat vodu?.DÄ›kuji za rady, tipy …..

  4. Vad roligt att sitta med i panelen :O)Är det Öland du bor pÃ¥?Ser ut som ni haft en toppendag!Snygga kläder, du passar bra i starka färger.Kram frÃ¥n en annan öbo 😉 /Milla

  5. Aww DebbiThese are all just amazing! I love all your lil details just adds that perfect finishing touch!Thanks for joining in the fun this time at Crafty Cardmakers!Hugs Melly xxx

  6. Ah jammer zeg! Ik vind 30 euro voor een tanktop eigenlijk best wel duur maar ik ben ook best wel arm. Vind je shorts heel cool zo!

Leave a Reply

Your email address will not be published. Required fields are marked *