How to find what depends on a stored procedure in Sybase

February 15, 2006 by 4 Comments

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

Related posts:

  1. Is Vertica’s product just Sybase IQ?
  2. sp_helprotect, not sp_helpprotect … can’t anyone at Sybase spell?!
  3. “SELECT DISTINCT … ORDER BY” is broken in Sybase ASE 12.5.3
  4. why can I never remember sp_helptext?
  5. How hard can it be to find geek friends?
Filed Under: Geeking out

Comments

  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

Speak Your Mind

*