Data Management

Finding dependencies in SQL Server 2005

The ability to view objects that are dependent upon other objects is useful when you need to alter or remove certain objects. Tim Chapman shows you how to write a procedure that will look up all of the objects that are dependent upon other objects.

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.

-----------------------------------------------------------------------------------------

Get database tips in your inbox

TechRepublic's free Database Management newsletter, delivered each Tuesday, contains hands-on SQL Server and Oracle tips and resources. Automatically subscribe today!

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

5 comments
jain747
jain747

In SQL Server 2000, there was the tree option to check dependencies on a table. Has that option been omitted in SQL Server 2005? Thanks.

StarNamer
StarNamer

Try the following in a test database: drop proc A drop proc B go create proc A as begin print 'a' end go create proc B as begin print 'b' exec A end go exec sp_FindDependencies 'A' You'll get 'B' reported as expected. Now execute... drop proc A go create proc A as begin print 'a 2' end go exec sp_FindDependencies 'A' You'll get nothing reported, but if you try 'exec B' then this will print the message from proc A indicating that it is dependent on it. Also management studio does not report any errors either when dropping or creating proc A so there's no indication that there's a problem. I've always found the dependency tree in SQL server to be pretty much useless since, in my experience, it's completely unreliable even when it could determine the dependencies. And, of course, if you start using dynamic SQL then it has no chance!

chapman.tim
chapman.tim

In a way, you're right. SQL Server has always had somewhat of a problem with keeping track of dependencies accurately. I think that it has gotten better w/ new releases, but its never 100% accurate...especially when you're deleting and modifying objects a lot.

Editor's Picks