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 (eg, 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.