Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

TSQL Lab 9 - How to find the dependency chain of a database object?

Sep 18 2008 7:59AM by Jacob Sebastian   

One of my colleague needed a piece of code that returns all the dependent objects of a given database object. His project has a separate reporting server to which the data from primary database is replicated. He creates the database objects needed for the reports (stored procedures, functions, views etc) and then update the article list of the publication with the new objects so that they get replicated to the report server.

Every time he needs to deploy a new report or modify an existing report, he needs to know what are the database objects that depend on the given report stored procedure. Some times the reports are very complex and each stored procedure might have dozens of dependent objects and each dependent object may be depending on other dozens of objects.

He needed a way to recursively find all the depending objects of a given stored procedure. I wrote a recursive query using CTE to achieve this. Before we look at the query, lets us create some test data.

USE XMLWorkshop
GO

IF OBJECT_ID('depCustomers') IS NOT NULL 
	DROP TABLE depCustomers
GO

CREATE TABLE depCustomers (
    DependsID INT,
    CustomerName VARCHAR(40),
    StatusID INT
)
GO

IF OBJECT_ID('GetCustomerStatus') IS NOT NULL 
	DROP FUNCTION GetCustomerStatus
GO
CREATE FUNCTION GetCustomerStatus
(
    @StatusID INT
)
RETURNS VARCHAR(20)
AS
BEGIN
    RETURN 'Active'
END
GO

IF OBJECT_ID('depCustomersView') IS NOT NULL 
	DROP VIEW depCustomersView
GO
CREATE VIEW depCustomersView
AS
SELECT 
    DependsID, 
    CustomerName, 
    StatusID
FROM depCustomers
GO

IF OBJECT_ID('depCustomersActive') IS NOT NULL 
	DROP VIEW depCustomersActive
GO
CREATE VIEW depCustomersActive
AS
SELECT 
    DependsID, 
    CustomerName, 
    StatusID,
    dbo.GetCustomerStatus(StatusID) AS StatusText
FROM depCustomersView
WHERE StatusID = 1

GO

IF OBJECT_ID('depGetCustomerList') IS NOT NULL 
	DROP PROCEDURE depGetCustomerList
GO
CREATE PROCEDURE depGetCustomerList
AS
SELECT * FROM depCustomersActive
GO

Note that we have created a few objects that creates a hierarchy of dependencies. The procedure "depGetCustomerList" takes data from view "depCustomersActive" which depends on a function and another child view. Let us now write a function that builds this dependency relationship and returns the all the objects that depend on the stored procedure: "depGetCustomerList".

Here is the function:

CREATE FUNCTION GetDependents(
    @ObjectName AS SYSNAME
)
RETURNS @result TABLE (
	Seq INT IDENTITY, 
	ObjectName SYSNAME, 
	Hierarchy VARCHAR(128))
AS
BEGIN
    ;WITH Obj AS (
        SELECT DISTINCT
            s.id  AS ParentID,
            s.DepID AS ObjectID,
            o1.Name AS ParentName,
            o2.Name AS ChildName,
            QUOTENAME(sch1.name) + '.' + QUOTENAME(o1.Name) 
			+ '(' + RTRIM(o1.type) + ')' 
                COLLATE SQL_Latin1_General_CP1_CI_AS 
			AS ParentObject, 
            QUOTENAME(sch2.name) + '.' + QUOTENAME(o2.Name) 
			+ '(' + RTRIM(o2.type) + ')' 
                COLLATE SQL_Latin1_General_CP1_CI_AS AS ObjectName
        FROM sys.sysdepends s
        INNER JOIN sys.all_objects o1 ON s.id = o1.object_id
        INNER JOIN sys.schemas sch1 ON sch1.schema_id = o1.schema_id
        INNER JOIN sys.all_objects o2 on s.DepID = o2.object_id
        INNER JOIN sys.schemas sch2 ON sch2.schema_id = o2.schema_id
    ), cte AS (
        SELECT 
            0 AS lvl, 
            ParentID,
            ObjectId,
            ParentObject,
            ObjectName,
            CAST(ObjectID AS VARBINARY(512)) AS Sort
        FROM obj WHERE ParentName = @ObjectName
        UNION ALL 
        SELECT
            p.lvl+ 1, 
            c.ParentID,
            c.ObjectId,
            c.ParentObject,
            c.ObjectName,
            CAST(p.sort + CAST(c.ObjectID AS VARBINARY(16)) 
		AS VARBINARY(512))
        FROM cte p 
        INNER JOIN obj c ON p.ObjectID = c.ParentID
    )
    INSERT INTO @result (ObjectName, Hierarchy)
    SELECT 
        ObjectName,
        '|-' + REPLICATE('-',(lvl * 4)) + ObjectName 
    FROM cte
    ORDER BY Sort
    
    RETURN 
END

Now let us test this function. Run the following code to retrieve the dependency relationship we discussed earlier.

SELECT * FROM dbo.GetDependents('depGetCustomerList')
/*
Sr ObjectName                    Hierarchy
-- ----------------------------- -----------------------------------
1  [dbo].[depCustomersActive](V) |-[dbo].[depCustomersActive](V)    
2  [dbo].[depCustomersView](V)   |-----[dbo].[depCustomersView](V)  
3  [dbo].[depCustomers](U)       |---------[dbo].[depCustomers](U)  
4  [dbo].[GetCustomerStatus](FN) |-----[dbo].[GetCustomerStatus](FN)
*/

Tags: TSQL,


Jacob Sebastian
1 · 100% · 32235
3
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

3  Comments  

  • Thanks for this really useful piece of code.

    However, I had a problem with it giving a recursion limit error on some objects, but fixed it by adding a "WHERE o1.object_id <> o2.object_id" clause to the first select in the function. For some reason I had objects that referenced themselves as parents, although why that should be the case I don't know at this point.

    However, I thought I'd comment in case anyone else has the same problem.

    commented on Mar 12 2012 6:53AM
    dpryde
    3057 · 0% · 2
  • Thanks for sharing it.

    commented on Mar 12 2012 7:29AM
    Jacob Sebastian
    1 · 100% · 32235
  • Hello All,

    I get the same object in the dependency list. How can I get the objects reference list?

    commented on Mar 23 2014 11:50AM
    anumal
    3057 · 0% · 2

Your Comment


Sign Up or Login to post a comment.

"TSQL Lab 9 - How to find the dependency chain of a database object?" rated 5 out of 5 by 3 readers
TSQL Lab 9 - How to find the dependency chain of a database object? , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]