Finding stored procedure table dependencies

posted Wednesday, June 11, 2003 7:09 PM

Recently one of my co-workers dropped me an IM asking how he could find what stored procedures depended on a certain table.  After a little digging we came up with the following query which will return a listing of stored procedures that depend on a particular table (T_TableName). 

select distinct so.name from syscomments sc
inner join sysobjects so on sc.id = so.id
where xtype = 'P' AND charindex('T_TableName', text) > 0

It would be cool if within SqlBuddy, VS.NET, SQL Enterprise Manager, (name your favorite DB tool) you could right click on a table and view stored procedure dependencies!

Update: For those of you interested in the real way to do this use sp_depends, thanks Darrell! 

9 Comments

# re: Finding stored procedure table dependencies

Thursday, June 12, 2003 5:40 AM by Darrell    
You can also use the sp_depends stored proc, it works for any object (table, stored proc, view, UDF, etc.) and is easier to use.



sp_depends 'T_TableName'



From SQL Server BOL:

"the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure"


# re: Finding stored procedure table dependencies

Thursday, June 12, 2003 5:40 AM by Darrell    
You can also use the sp_depends stored proc, it works for any object (table, stored proc, view, UDF, etc.) and is easier to use.



sp_depends 'T_TableName'



From SQL Server BOL:

"the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure"


# re: Finding stored procedure table dependencies

Thursday, January 29, 2004 5:25 AM by Chris Taylor    
Bit late I know, but...

The sp_depends stored procedure relies on the sysdepends table, and unfortunately this information can not be relied on. The contents of sysdepends is sensitive to the order that the database objects are created.

# re: Finding stored procedure table dependencies

Thursday, January 29, 2004 5:26 AM by Steve    
So are you saying my method actually could be useful? Cool!

# re: Finding stored procedure table dependencies

Tuesday, February 10, 2004 6:57 AM by Calum    
Sadly, in the real world, the sp_depends and sysdepends table in SQL Server is just not up to scratch. I have a stored proc that references a table and a function. No matter what the order is the objects are created, the dependancies are never created correctly.

Using syscomments is probably the best method to get the actualy dependant objects, but will take a little bit of a fiddle - grep/awk/sed or your favourite search algorithm to successfully get the information.

Hopefully Yukon will address some of these failings .

# re: Finding stored procedure table dependencies

Tuesday, October 12, 2004 3:50 AM by Paul D    
Wouldn't it be cool if there was a way to update the Sysdepends table with, say....a DATABASE MAINTENANCE step??? *hands on hips*

# re: Finding stored procedure table dependencies

Monday, February 12, 2007 6:44 PM by Tom Winans    
It seems these methods might be dependent upon the language in which a stored proc is implemented ... T-SQL seems to behave nicely with sp_depends ... is this true for C#?

# re: Finding stored procedure table dependencies

Monday, February 25, 2008 6:34 PM by Bryon Pierce    
I often find this script useful when i want to find any string of text in my procedures and views.


Declare @text as varchar (25)
set @text = 'select'


SELECT DISTINCT Len(SubString(text,1, PatIndex('%' + @text + '%', text)))
-Len(Replace(SubString(text,1, PatIndex('%' + @text + '%', text)),
char(13),''))+1 AS Line,
PatIndex('%' + @text + '%', text) AS Position,
OBJECT_NAME(id) AS ProcName , @text as searchtext,SUBSTRing(text,
PatIndex('%' + @text + '%', text),100) as smalldescription
FROM syscomments
WHERE text like '%' + @text + '%'
ORDER BY ProcName, Line

# re: Finding stored procedure table dependencies

Tuesday, April 01, 2008 2:41 AM by Rob    
I had a hard time finding something to find the table dependencies for SQL Procs. Then I found a query that uses sysdepends and sysobjects. However it does not give you dependencies across databases. Anyways I wrote a quick app for this. If you want a copy send me an email CodeMasterRobertoMilian@gmail.com I don't have an FTP site to post this to but I would be glad to share...

Post a Comment

 
 
Prove you're not a spammer: 
0 + 1 =