«
»

How to find what depends on a stored procedure in Sybase

Oracle really spoils you with the ALL_DEPENDENCIES data dictionary view. If you want to know what depends on what, you just query it. These days, I’m working in Sybase again, so I had to figure out how to do it using Sybase’s data dictionary views. Hopefully you’re looking for the same thing and this will be useful to you:

SELECT o.id, o.name, o.type
FROM sysobjects AS o, sysobjects AS do, sysdepends AS d
WHERE do.name = 'procedureName'
AND d.depid = do.id
AND o.id = d.id

Just replace 'procedureName' with the name of the procedure you’re interested in.

Tags:
database,
sql,
oracle,
sybase

This entry was posted by Dossy Shiobara on Wednesday, February 15th, 2006 at 1:48 pm and is filed under Geeking out. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

No Responses to “How to find what depends on a stored procedure in Sybase”

  1. Steve Goguen says:
    February 15, 2006 at 2:51 pm

    This also works with SQL Server.

    Reply
  2. Dossy says:
    February 15, 2006 at 3:02 pm

    Did you verify that by trying it, or just assume it should because they share the same ancestry? I wonder if Sybase and MS SQL Server’s data dictionary views have diverged at some point. Probably not radically, I bet.

    Reply
  3. Stephen Goguen says:
    February 15, 2006 at 10:59 pm

    Actually verified it. I’ve been using similar code to search for keywords in stored procedure definitions, etc.

    Do you guys use a lot of Sybase at AOL?

    Reply
  4. Dossy says:
    February 15, 2006 at 11:03 pm

    Unfortunately, yes. Someone a long time ago thought it was a good idea, and you know how hard it is to replace entrenched technologies.

    However, we do have a good amount of MySQL and apparently even some Oracle instances. But, there’s still some systems where the data lives in Sybase. Sigh.

    Reply

Leave a Reply