Find recursive dependencies between SQL objects

Ever needed to see the dependencies between objects in SQL Server?

Here is a trick to list all the objects (view, sp, function, etc) having dependencies on a given object (let's call it the root object). Of course, this code returns the dependencies of the dependencies in a recursive way, so that you can find all the objects that use the root, even if it is through intermediate objects.

Basically, it queries the system view sys.sql_expression_dependencies to get the objects using the root, it creates the level-1 dependencies. Then it iterates over the same system view to get all the objects using level-1 objects, creating level-2 dependencies. Next iteration creates set of level-3 dependencies, and so on, until no more dependencies are found.
Note that it could have been done with recursive-CTE too, but we would have to cope with tricky diamond-cases. So I chose the old-school solution, with iterations.

CREATE FUNCTION dbo.GetDependencies
(
 @SchemaName VARCHAR(255),
 @ObjectName VARCHAR(255)
)
RETURNS 
@Dependencies TABLE 
(
 Referenced INT NOT NULL,
 Referencing INT NOT NULL,
 Depth INT NOT NULL
)
AS
BEGIN
 DECLARE @CurrentDepth INT = 1
 DECLARE @NbRowsInserted INT

 INSERT INTO @Dependencies
 SELECT d.referenced_id, d.referencing_id, @CurrentDepth
 FROM sys.sql_expression_dependencies d
 WHERE d.referenced_id =
 (
  SELECT o.object_id
  FROM sys.objects o
  INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
  WHERE o.name = @ObjectName
  AND s.name = @SchemaName
 )
 AND d.referenced_id <> d.referencing_id

 SET @NbRowsInserted = @@ROWCOUNT

 WHILE (@NbRowsInserted > 0)
 BEGIN
  SET @CurrentDepth = @CurrentDepth + 1
  
  INSERT INTO @Dependencies
  SELECT d.referenced_id, d.referencing_id, @CurrentDepth
  FROM sys.sql_expression_dependencies d
  INNER JOIN
  (
   SELECT DISTINCT dd.Referencing
   FROM @Dependencies dd
   WHERE dd.Depth = @CurrentDepth - 1
  ) dd ON dd.Referencing = d.referenced_id
  LEFT JOIN
  (
   SELECT DISTINCT ddd.Referenced, ddd.Referencing
   FROM @Dependencies ddd
  ) ddd ON ddd.Referenced = d.referenced_id AND ddd.Referencing = d.referencing_id
  WHERE ddd.Referenced IS NULL /* Not a already referenced object, to avoid infinite loops */
  AND d.referenced_id <> d.referencing_id

  SET @NbRowsInserted = @@ROWCOUNT
 END
 
 RETURN 
END

As an example, let's create some dependent SQL objects, and ask for the dependencies:

CREATE TABLE dbo.RD_ROOT ( Foo INT, Bar INT)
GO
CREATE VIEW dbo.RD_View_1 AS SELECT Foo, Bar, Bar + 1 AS NewBar FROM dbo.RD_ROOT
GO
CREATE VIEW dbo.RD_View_2 AS SELECT Foo, NewBar * 2 AS NewNewBar FROM dbo.RD_VIEW_1
GO
CREATE PROCEDURE dbo.RD_SP_1 AS BEGIN SELECT Foo, NewBar FROM dbo.RD_View_1 END
GO
CREATE PROCEDURE dbo.RD_SP_2 AS BEGIN SELECT Foo, NewNewBar FROM dbo.RD_View_2 END
GO
SELECT *, OBJECT_NAME(Referenced) AS ReferencedName, OBJECT_NAME(Referencing) AS ReferencingName FROM TEC.FN_GET_DEPENDENCY('dbo', 'RD_ROOT')

It returns the following infos:

It just says that View_1 depends on Root, View_1 depends on View_2, SP_1 depends on View_1 too, and SP_2 depends on View_2.

Here is another way to display it, maybe a bit more readable, including the type of each object:

DECLARE @Dependencies TABLE 
(
 Referenced INT NOT NULL,
 Referencing INT NOT NULL,
 Depth INT NOT NULL
)

INSERT INTO @Dependencies
SELECT * FROM dbo.GetDependencies('dbo', 'RD_ROOT')

;

WITH RichDependencies AS
(
 SELECT
  d.Referencing AS ReferencingId,
  '[' + s.name + '].[' + o.name + '] (' + o.type + ')' AS ReferencingName,
  s.name AS ReferencingSchemaName,
  o.name AS ReferencingObjectName,
  o.type_desc AS ReferencingType,
  d.Depth AS DependencyDepth,
  d.Referenced AS ReferencedId
 FROM @Dependencies d
 INNER JOIN sys.objects o ON d.Referencing = o.object_id
 INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
 UNION ALL
 SELECT
  o.object_id AS ReferencingId,
  '[' + s.name + '].[' + o.name + '] (' + o.type + ')' COLLATE SQL_Latin1_General_CP1_CI_AS AS ReferencingName,
  s.name AS ReferencingSchemaName,
  o.name AS ReferencingObjectName,
  o.type_desc AS ReferencingType,
  0 AS DependencyDepth,
  NULL AS ReferencedId
 FROM sys.objects o
 INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
 WHERE o.object_id = (SELECT MIN(Referenced) FROM @Dependencies WHERE Depth = 1)
)
SELECT
 d0.ReferencingName AS Level_0,
 d1.ReferencingName AS Level_1,
 d2.ReferencingName AS Level_2,
 d3.ReferencingName AS Level_3,
 d4.ReferencingName AS Level_4,
 d5.ReferencingName AS Level_5,
 d6.ReferencingName AS Level_6,
 d7.ReferencingName AS Level_7,
 d8.ReferencingName AS Level_8,
 d9.ReferencingName AS Level_9
FROM RichDependencies d0
LEFT JOIN RichDependencies d1 ON d1.ReferencedId = d0.ReferencingId AND d1.DependencyDepth = d0.DependencyDepth + 1
LEFT JOIN RichDependencies d2 ON d2.ReferencedId = d1.ReferencingId AND d2.DependencyDepth = d1.DependencyDepth + 1
LEFT JOIN RichDependencies d3 ON d3.ReferencedId = d2.ReferencingId AND d3.DependencyDepth = d2.DependencyDepth + 1
LEFT JOIN RichDependencies d4 ON d4.ReferencedId = d3.ReferencingId AND d4.DependencyDepth = d3.DependencyDepth + 1
LEFT JOIN RichDependencies d5 ON d5.ReferencedId = d4.ReferencingId AND d5.DependencyDepth = d4.DependencyDepth + 1
LEFT JOIN RichDependencies d6 ON d6.ReferencedId = d5.ReferencingId AND d6.DependencyDepth = d5.DependencyDepth + 1
LEFT JOIN RichDependencies d7 ON d7.ReferencedId = d6.ReferencingId AND d7.DependencyDepth = d6.DependencyDepth + 1
LEFT JOIN RichDependencies d8 ON d8.ReferencedId = d7.ReferencingId AND d8.DependencyDepth = d7.DependencyDepth + 1
LEFT JOIN RichDependencies d9 ON d9.ReferencedId = d8.ReferencingId AND d9.DependencyDepth = d8.DependencyDepth + 1
WHERE d0.DependencyDepth = 0
ORDER BY
 d0.ReferencingName, d1.ReferencingName, d2.ReferencingName, d3.ReferencingName, d4.ReferencingName, 
 d5.ReferencingName, d6.ReferencingName, d7.ReferencingName, d8.ReferencingName, d9.ReferencingName

Have fun!