Listing SQL Server Object Dependencies

By:   |   Updated: 2007-07-26   |   Comments (10)   |   Related: 1 | 2 | 3 | 4 | 5 | More > Comparison Data and Objects

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?

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?


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


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
FROM syscomments sc
INNER JOIN sysobjects so ON =
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,

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

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
exec b

-- 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
exec a

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



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
FROM syscomments sc
INNER JOIN sysobjects so ON =
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

Last Updated: 2007-07-26

get scripts

next tip button

About the author

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

Wednesday, April 15, 2015 - 11:27:11 PM - cornstarch Back To Top

I was wondering if there is a method out there to get all objects cross database/server that referenced a table. 






Saturday, December 07, 2013 - 2:04:36 AM - Pramod Sharma Back To Top

Great!!! thanks.

Sunday, December 01, 2013 - 1:09:52 AM - MOhammad Back To Top

Very good . thanks

Thursday, August 29, 2013 - 12:41:31 AM - Adam Back To Top

Great tips.

Although it's not always possible to find all dependencies quickly and efficiently.

I stumbled across SQL Negotiator Pro available from this application graphically draws all dependencies with the capability of adding notes and exporting. Very nice!



Monday, April 29, 2013 - 1:18:49 PM - mohamed Back To Top

thanks a lot,it's a great tips  

Thursday, January 31, 2013 - 7:29:40 PM - Lan Nguyen Back To Top

Thank you!

Thursday, July 05, 2012 - 2:35:57 AM - venushakamuri Back To Top

very good Post ...............................

Wednesday, July 04, 2012 - 7:49:45 AM - Aj Back To Top

Great!! Thanks

Monday, March 26, 2012 - 6:15:37 AM - Anna Back To Top

Awesome! Thanks a lot.

Wednesday, September 17, 2008 - 8:31:52 PM - jacobsebastian Back To Top

 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:


Jacob Sebastian


Recommended Reading

Identifying Object Dependencies in SQL Server

Different Ways to Find SQL Server Object Dependencies

Finding SQL Server Object Dependencies with DMVs

Finding SQL Server Object Dependencies for Synonyms

Ways to compare and find differences for SQL Server tables and data

get free sql tips
agree to terms

Learn more about SQL Server tools