Geek Answers Handbook

Query for recursively identifying object dependencies

I have a complex query with several tables, views and functions inside it. Functions and representations are divided into a larger number of representations and functions that can potentially be divided into several types and functions within them.

There are performance issues in this question, so I want to get a clear and concise list of all the objects that are referenced in my query, so I have a basis for my investigation. How to get this list of objects?

+4
source share
5 answers

I saw this post to identify all objects referencing a particular synonym, and used the underlying logic in the response in a recursive CTE to identify all objects associated with a comma-separated list of objects in 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;
+5
source

DESCRIPTION

Wrote this stored procedure below which RECURSIVELY lists all dependent child objects and child children and child ... etc. Input parameter can be saved in Proc, User Function, View. You can easily modify it to get a unique list of columns 5, no matter what level the Object called, and how deep and what the object is.

COLUMN

  • UsedByObjectId - the parent object that uses the dependent object
  • UsedByObjectName - name of the parent object
  • UsedByObjectType - type of the parent object (P, V, FN)
  • DependentObjectId - a child that uses the parent
  • DependentObjectName - name of the child
  • DependentObjectType - type of dependent child (P, V, FN, U)
  • Level . How deep is the nested recursive level in which this object is used.

CODE

 --========================================================================= --========================================================================= --== utlGetAllDependentObjectsRecursive - Uses recursive common table --== expression to recursively get all the dependent objects as well --== as the child objects and child 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
+4
source

SQL Server 2008 introduced two new dynamic management features for tracking object dependencies: sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities :

1 / Return of objects related to this object:

 SELECT referencing_schema_name, referencing_entity_name, referencing_class_desc, is_caller_dependent FROM sys.dm_sql_referencing_entities ('<TableName>', 'OBJECT')

2 / Returned objects referenced by the 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 the rather useful SQL Dependency Tracker tool from Red Gate.

+3
source

Check this one, you will get all recursive objects.

  WITH Refobjects (referencing_object_name,referencing_object_type_desc) AS ( SELECT o.name AS referencing_object_name, o.type_desc AS referencing_object_type_desc FROM sys.sql_expression_dependencies sed INNER JOIN sys.objects o ON sed.referencing_id = o.[object_id] WHERE sed.referenced_entity_name = 'Your Object Name' UNION ALL SELECT o.name AS referencing_object_name, o.type_desc AS referencing_object_type_desc FROM sys.sql_expression_dependencies sed INNER JOIN sys.objects o ON sed.referencing_id = o.[object_id] INNER JOIN Refobjects ON sed.referenced_entity_name = Refobjects.referencing_object_name ) SELECT distinct * FROM Refobjects Order by 2 desc,1 ;
+2
source

Based on @Raju Chavan's answer above, which works great. But...

I added schema support and also returned (and ordered) the recursion level, so it can be easily turned into a script to update object references in the correct order using sp_refreshsqlmodule (see point 3 below).

 WITH cRefobjects AS ( SELECT o.name, s.name AS sch, o.type_desc, 1 AS level FROM sys.sql_expression_dependencies sed INNER JOIN sys.objects o ON o.object_id = sed.referencing_id INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id WHERE (sed.referenced_schema_name = '<your schema>' OR sed.referenced_schema_name IS NULL) AND sed.referenced_entity_name = '<your object name>' UNION ALL SELECT o.name, s.name AS sch, o.type_desc, cRefobjects.level + 1 AS level FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects o ON o.object_id = sed.referencing_id INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id INNER JOIN cRefobjects ON sed.referenced_entity_name = cRefobjects.name AND sed.referenced_schema_name = cRefobjects.sch ) SELECT DISTINCT name, sch, type_desc, level FROM cRefobjects ORDER BY level, type_desc DESC, name;

A few things to consider:

  • Replace <your circuit> and <your target> with what you need.
  • If the referenced object does not have a schema prefix in the referenced object, the schema is virtually unknown, so the predicate above OR sed.referenced_schema_name IS NULL . This can lead to an invalid reference object if you do not use the best methods in your database objects.
  • My goal is to find and find this answer, to write a script to automatically update references to objects in the database after editing the view using sp_refreshsqlmodule . To do this, simply wrap the CTE shown above as follows. This prints the required SQL to update the link objects in the correct order:
 DECLARE @SQL NVARCHAR(4000); SET @SQL = ''; WITH cRefobjects AS ( ... ) --SELECT DISTINCT name, sch, type_desc, level SELECT @SQL = @SQL + 'EXEC sys.sp_refreshsqlmodule ''' + sch + '.' + name + '''' + CHAR(13)+CHAR(10) FROM cRefobjects ORDER BY level, type_desc DESC, name; PRINT @SQL
0
source

More articles:


All Articles