Query to Recursively Identify Object Dependencies
DESCRIPTION
Wrote this Stored Procedure below which RECURSIVELY lists all the dependent child objects and child's dependent objects and child's child...etc. The input parameter can be Stored Proc, User Function, View. Can easily be altered to get a Unique List of Column 5, regardless of what Level the Object was called and how deep and by which object.
COLUMNS
- UsedByObjectId - The parent object that uses the dependent object
- UsedByObjectName - The name of the parent object
- UsedByObjectType - Type of the parent object (P,V,FN)
- DependentObjectId - The child object the parent uses
- DependentObjectName - Name of the child object
- DependentObjectType - Type of the dependent child object (P,V,FN, U)
- Level - How deep, the nested recursive level which the object is used
THE CODE
--=========================================================================
--=========================================================================
--== utlGetAllDependentObjectsRecursive - Uses recursive common table
--== expression to recursively get all the dependent objects as well
--== as the child objects and child's child objects of a
--== Stored Procedure or View or Function. can be easily modified to
--== include all other types of Objects
--=========================================================================
--=========================================================================
CREATE PROCEDURE utlGetAllDependentObjectsRecursive
(
-- Supports Stored Proc, View, User Function, User Table
@PARAM_OBJECT_NAME VARCHAR(500)
)
AS
BEGIN
WITH CTE_DependentObjects AS
(
SELECT DISTINCT
b.object_id AS UsedByObjectId,
b.name AS UsedByObjectName, b.type AS UsedByObjectType,
c.object_id AS DependentObjectId,
c.name AS DependentObjectName , c.type AS DependenObjectType
FROM sys.sysdepends a
INNER JOIN sys.objects b ON a.id = b.object_id
INNER JOIN sys.objects c ON a.depid = c.object_id
WHERE b.type IN ('P','V', 'FN') AND c.type IN ('U', 'P', 'V', 'FN')
),
CTE_DependentObjects2 AS
(
SELECT
UsedByObjectId, UsedByObjectName, UsedByObjectType,
DependentObjectId, DependentObjectName, DependenObjectType,
1 AS Level
FROM CTE_DependentObjects a
WHERE a.UsedByObjectName = @PARAM_OBJECT_NAME
UNION ALL
SELECT
a.UsedByObjectId, a.UsedByObjectName, a.UsedByObjectType,
a.DependentObjectId, a.DependentObjectName, a.DependenObjectType,
(b.Level + 1) AS Level
FROM CTE_DependentObjects a
INNER JOIN CTE_DependentObjects2 b
ON a.UsedByObjectName = b.DependentObjectName
)
SELECT DISTINCT * FROM CTE_DependentObjects2
ORDER BY Level, DependentObjectName
END
I saw this post to identify all the objects that reference a particular synonym and used the base logic in the answer in a recursive CTE to identify all the objects related to a comma-delimited list of the objects within the top level query being executed.
Declare @baseObjects Nvarchar(1000) = '[Schema].[Table],[Schema].[View],[Schema].[Function],[Schema].[StoredProc]',
@SQL Nvarchar(Max);
Declare @objects Table (SchemaName Varchar(512), TableName Varchar(512), ID Int, xtype Varchar(10));
Set @SQL = 'Select ss.name As SchemaName,
so.name As TableName,
so.id,
so.xtype
From sysobjects so
Join sys.schemas ss
On so.uid = ss.schema_id
Where so.id In (Object_ID(''' + Replace(@baseObjects,',','''),Object_ID(''') + '''))';
Insert @objects
Exec sp_executeSQL @SQL;
With test As
(
Select ss.name As SchemaName,
so.name As TableName,
so.id,
so.xtype
From sys.sql_expression_dependencies sed
Join @objects vo
On sed.referencing_id = vo.ID
Join sysobjects so
On sed.referenced_id = so.id
Join sys.schemas ss
On so.uid = ss.schema_id
Union All
Select ss.name As SchemaName,
so.name As TableName,
so.id,
so.xtype
From test
Join sys.sql_expression_dependencies sed
On sed.referencing_id = test.id
And sed.referencing_id <> sed.referenced_id
Join sysobjects so
On sed. referenced_id = so.id
Join sys.schemas ss
On so.uid = ss.schema_id
)
Select Distinct *
From test
Union
Select *
From @objects;
In SQL Server 2008 there are two new Dynamic Management Functions introduced to keep track of object dependencies: sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities:
1/ Returning the entities that refer to a given entity:
SELECT
referencing_schema_name, referencing_entity_name,
referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('<TableName>', 'OBJECT')
2/ Returning entities that are referenced by an object:
SELECT
referenced_schema_name, referenced_entity_name, referenced_minor_name,
referenced_class_desc, is_caller_dependent, is_ambiguous
FROM sys.dm_sql_referenced_entities ('<StoredProcedureName>', 'OBJECT');
Another option is to use a pretty useful tool called SQL Dependency Tracker from Red Gate.