Listing SQL Server Object Dependencies

By: Greg Robidoux   |   Read Comments (6)   |   Related Tips: More > Comparison - Data and Objects

Problem
When developing components for SQL Server one change may affect another database object.  Finding these dependent objects should be straightforward, but in most cases it is not as easy as you would think.  So what is the best way to find dependency information in SQL Server?

Solution
There are several methods of getting this information.  The first approach would be to use the SQL Server Management tools. 

For SQL Server 2005, right click on the table name and select "View Dependencies" as shown below we are looking at dependencies for the Employee table.

This will give you the following view so you can see objects that are dependent on the Employee table.

And this next screen shot shows you objects that table Employee depends upon.

To get this information, SQL Server does a lot of work to get.  To see the process that SQL Server uses to generate this data for this screen click here.

 

Although this is helpful to get the data via the GUI, what other approaches are there to get this data?

Method 1 - INFORMATION_SCHEMA.ROUTINES

This approach uses INFORMATION_SCHEMA.ROUTINES to search through the definition of the routine such as the stored procedure, trigger, etc...

SELECT routine_name, routine_type FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%Employee%'

Method 2 - sp_depends

This approach uses the system stored procedure sp_depends.

EXEC sp_depends @objname = N'HumanResources.Employee' ;

Method 3 - Using syscomments

This approach reads data from the syscomments table.  This is similar to method 1.

SELECT distinct so.name FROM syscomments sc INNER JOIN sysobjects so ON sc.id = so.id WHERE charindex('Employee', text) > 0

Method 4 - sp_MSdependencies

This approach uses the system stored procedure sp_MSdependencies.

-- Value 131527 shows objects that are dependent on the specified object EXEC sp_MSdependencies N'HumanResources.[Employee]', null, 1315327

-- Value 1053183 shows objects that the specified object is dependent on EXEC sp_MSdependencies N'HumanResources.[Employee]', null, 1053183

Summary
As you can see from these different methods each gives you a different part of the answer.  So to be safe none of these approaches is full proof.  To get a complete listing you really need to employ a few different methods to ensure you are not missing anything.

 

To take this a step further let's take a quick look at creating some objects to see what these different methods return.  For this next example we will create two stored procedures that rely on each other.  This is probably not something you would do, but this helps illustrate the issue.

create proc a as exec b go -- after creating proc a we get this error message -- Cannot add rows to sysdepends for the current object because it depends on the missing object 'b'. The object will still be created. create proc b as exec a go

Now let's see what each of these methods returns after these two stored procedures have been created.

Method 1 - INFORMATION_SCHEMA.ROUTINES

SELECT routine_name, routine_type FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%a%'

This returns the following showing both proc a and proc b.

Method 2 - sp_depends

exec sp_depends a

This returns the following showing only that proc b depends on proc a.

Method 3 - Using syscomments

SELECT distinct so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id = so.id
WHERE charindex('a', text) > 0

This returns the following showing both proc a and proc b.

Method 4 - sp_MSdependencies

EXEC sp_MSdependencies N'dbo.a', null, 1315327
EXEC sp_MSdependencies N'dbo.a', null, 1053183

This returns the following showing only that proc b depends on proc a.

Management Studio

This returns the following showing only that proc b depends on proc a.

So from this test the only two processes that returned the results we were expecting are Method 1 and Method 3.

Since stored procedure "b" did not exist when we created procedure "a" this dependency data does not exist.  If we alter stored procedure "a" and save the procedure the dependencies should be updated correctly as shown below.  This would be true for both sp_depends, sp_MSdependencies and SQL Server Management Studio.

 

As you have seen there are some cases where this information is reliable and others where the data is not reliable.  Make sure you check all methods before determining whether you have a complete list of all your object dependencies.

Next Steps

• Take a look at these other related articles
  • BUG: SP_Depends Does Not List Triggers
  • Object Dependency Hierarchal Relationships
• Products that allow you to see your dependencies
  • SQL Dependency Tracker

     

Last Update: 7/26/2007

About the author

Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips

We Recommend

• More SQL Server DBA Tips...

Comments and Feedback:

Wednesday, September 17, 2008 - 8:31:52 PM - jacobsebastian	Read The Tip
Great post! For SQL Server 2005, I wrote a table valued function that implements a recursive CTE to return the entire dependency chain of given object. The code is published here: http://www.sqlserverandxml.com/2008/09/find-dependent-objects-recursively.html Regards Jacob Sebastian

Monday, March 26, 2012 - 6:15:37 AM - Anna	Read The Tip
Awesome! Thanks a lot.

Wednesday, July 04, 2012 - 7:49:45 AM - Aj	Read The Tip
Great!! Thanks

Thursday, July 05, 2012 - 2:35:57 AM - venushakamuri	Read The Tip
very good Post ...............................

Thursday, January 31, 2013 - 7:29:40 PM - Lan Nguyen	Read The Tip
Thank you!

Monday, April 29, 2013 - 1:18:49 PM - mohamed	Read The Tip
thanks a lot,it's a great tips

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis! SQL Monitor: prioritize your SQL Server workload with easy-to-use performance monitoring Wish your SQL Servers could run wide open? Learn how the Edgewood SQL Server Consultants can make it happen. Spring Clean Your Data - Clean your global contact data with Melissa Data tools for SSIS. Download a free trial! Optimizing SQL Server performance can be a daunting task. Or is it?