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)
*/