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

Comments

  1. This also works with SQL Server.

  2. 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.

  3. 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?

  4. 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.

Speak Your Mind

*