Listing SQL Server Object Dependencies

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?

SolutionThere 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

SummaryAs 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.

Source Collected from MSSQLTIPS.COM