A Few of My Favorite Queries: List the stored procedures that reference a certain table.

As a consultant, I spend a lot of time trying to get up to speed on new Database Models. This little batch has come in handy many a time, as it allows you to easily list the stored procedures which reference a given table.

 BEGIN   DECLARE @tableName sysname  SET @tableName = 'gisshipment_term'  

SELECT DISTINCT(dependentobjects.name)
FROM sysobjects targetobject
INNER JOIN sysdepends depends
ON targetobject.id = depends.depid
INNER JOIN sysobjects dependentobjects
ON depends.id = dependentobjects.id
WHERE targetobject.name LIKE @tableName
AND dependentobjects.xtype = 'P'

That’s one powerful little query!

I know what you’re thinking, can’t I just use sp_depends? The answer is, you’re right. I like this method though as it let’s me use wildcards in the search term.