Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Listing SQL Server Object Dependencies

MSSQLTips author Greg Robidoux By:   |   Read Comments (10)   |   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



Last Update: 7/26/2007


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Saturday, December 07, 2013 - 2:04:36 AM - Pramod Sharma Read The Tip

Great!!! thanks.


Sunday, December 01, 2013 - 1:09:52 AM - MOhammad Read The Tip

Very good . thanks


Thursday, September 12, 2013 - 3:36:06 PM - Evgeny Read The Tip

There is a free plug-in SQL Refactor Studio (http://sqlrefactorstudio.com/) for SQL Server Management Studio allows you to perform advanced search dependencies for database objects.

The search is performed in

- Databases

- Files on disk

- Query Editor



Thursday, August 29, 2013 - 12:41:31 AM - Adam Read The Tip

Great tips.

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

I stumbled across SQL Negotiator Pro available from www.aphilen.com 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 Read The Tip

thanks a lot,it's a great tips  


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

Thank you!


Thursday, July 05, 2012 - 2:35:57 AM - venushakamuri Read The Tip

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


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

Great!! Thanks


Monday, March 26, 2012 - 6:15:37 AM - Anna Read The Tip

Awesome! Thanks a lot.


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




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.