Tech

Finding dependencies in SQL Server 2005

Any time you need to modify objects in your SQL Server 2005 database, the objects that are dependent upon those objects are a concern. You don't want to remove columns from tables, procedures, views, or tables if there are objects dependent upon them that are being used.

This tutorial will show how you can write a procedure that will look up all of the objects that are dependent upon other objects.

How to write the procedure
To start a dependency chain, I create a table and then create some objects that will depend upon that table. Below is a script to create my SalesHistory and load some data into it:

IF OBJECT_ID('SalesHistory')>0     
 DROP TABLE SalesHistory;
 GO
 CREATE TABLE [dbo].[SalesHistory]
 (     
         [SaleID] [int] IDENTITY(1,1) NOT NULL 
PRIMARY KEY,     
         [Product] [char](150) NULL,     
         [SaleDate] [datetime] NULL,     
         [SalePrice] [money] NULL
 )
 GO    

 DECLARE @i SMALLINT
 SET @i = 1
 WHILE (@i <=100)
 BEGIN                 
       INSERT INTO SalesHistory              
       (Product, SaleDate, SalePrice)                 
       VALUES     
 ('Computer', DATEADD(mm, @i, '3/11/1919'), 
DATEPART(ms, GETDATE()) + (@i + 57))          

       INSERT INTO SalesHistory               
       (Product, SaleDate, SalePrice)     
       VALUES            
 ('BigScreen', DATEADD(mm, @i, '3/11/1927'), 
DATEPART(ms, GETDATE()) + (@i + 13))                      

       INSERT INTO SalesHistory                 
       (Product, SaleDate, SalePrice)         
       VALUES           
 ('PoolTable', DATEADD(mm, @i, '3/11/1908'), 
DATEPART(ms, GETDATE()) + (@i + 29))                      

       SET @i = @i + 1    

 END

I'll create a couple of objects that are dependent upon the SalesHistory table. This view uses the DENSE_RANK ranking function to return the sales rank of each product based on when the product was entered into the table. This view is directly dependent upon the SalesHistory table.

CREATE VIEW vw_SalesHistory
 AS
        SELECT SaleRank = DENSE_RANK() OVER (PARTITION BY 
Product ORDER BY SaleID ASC), *
         FROM SalesHistory
 GO

The stored procedure returns the total sales for the Computer product group. This procedure uses the view that I just created, so it is dependent upon that view, which is dependent upon the SalesHistory table. In a sense, this creates a dependency chain.

CREATE PROCEDURE usp_GetTotalComputerSales
 (
         @TotalSales MONEY OUTPUT
 )
 AS
 BEGIN
         SELECT @TotalSales = SUM(SalePrice)
         FROM vw_SalesHistory
         WHERE Product = 'Computer'
 END
 GO

Here is the code to create the system stored procedure for finding object dependencies:

USE master
 GO
 CREATE PROCEDURE sp_FindDependencies
 (
         @ObjectName SYSNAME,
         @ObjectType VARCHAR(5) = NULL
 )
 AS
 BEGIN
     DECLARE @ObjectID AS BIGINT    

         SELECT TOP(1) @ObjectID = object_id
         FROM sys.objects
         WHERE name = @ObjectName
         AND type = ISNULL(@ObjectType, type)    

     SET NOCOUNT ON ;    

       WITH DependentObjectCTE (DependentObjectID, 
DependentObjectName, ReferencedObjectName, ReferencedObjectID)
         AS
         (
         SELECT DISTINCT
                sd.object_id,
                OBJECT_NAME(sd.object_id),
                ReferencedObject = OBJECT_NAME
(sd.referenced_major_id),
                ReferencedObjectID = sd.referenced_major_id
         FROM    
                sys.sql_dependencies sd
                JOIN sys.objects so ON sd.referenced_major_id = 
so.object_id
         WHERE   
                sd.referenced_major_id = @ObjectID
         UNION ALL
         SELECT
                sd.object_id,
                OBJECT_NAME(sd.object_id),
                OBJECT_NAME(referenced_major_id),
                object_id
         FROM    
                sys.sql_dependencies sd
             JOIN DependentObjectCTE do ON sd.referenced_major_id 
= do.DependentObjectID       
         WHERE
                sd.referenced_major_id <> sd.object_id     
         )
         SELECT DISTINCT
                DependentObjectName
         FROM   
                DependentObjectCTE c
 END

This procedure uses a Common Table Expression (CTE) with recursion to walk down the dependency chain to get to all of the objects that are dependent on the object passed into the procedure. The main source of data comes from the system view sys.sql_dependencies, which contains dependency information for all of your objects in the database.

Note: There are exceptions to this table. SQL Server 2005 will only place data into the sys.sql_dependencies view if it is able to at the creation of the object. If the database is not able to add a dependency, it will let you know at the time the object is created.

I want to mark the stored procedure as a system stored procedure so I can call it for any object in any database.

EXECUTE sp_ms_marksystemobject 'sp_FindDependencies'

Now I can call my new system stored procedure to find any objects that are dependent upon the SalesHistory table that I just created.

EXECUTE sp_FindDependencies 'SalesHistory'

I get the results that I expect from the procedure. The following objects are returned:

usp_GetTotalComputerSales
 vw_SalesHistory

The view vw_SalesHistory is returned because it is directly dependent upon the SalesHistory table. The procedure usp_GetTotalComputerSales is returned because it is dependent upon the view vw_SalesHistory, which in turn is dependent upon the SalesHistory table.

Use with caution
The ability to view objects that are dependent upon other objects (e.g., views that use tables, procedures that use views) is useful when you need to alter or remove certain objects. Be extra careful when you modify objects that other objects may depend on.

Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.