Listing SQL Server Object Dependencies
Written By: Greg Robidoux -- 7/26/2007
-- read/post comments
-- print --
Rating:
(not rated yet)
Rate
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
- Products that allow you to see your dependencies
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
