Rekursiv Abhängigkeiten von Datenbankobjekten ermitteln / Recursively determine dependencies of database objects

Das nachfolgende T-SQL-Script erlaubt das Auffinden von Abhängigkeiten zwischen Datenbankobjekten.

Die Prüfung erfolgt über alle abhängigen SQL-Sichten, Datenbankprozeduren und Funktionen.

The following T-SQL script allows finding dependencies between database objects.

The check is carried out using all dependent SQL views, database procedures and functions.

DECLARE @sSchema_Name AS VARCHAR(64)
DECLARE @sObject_Name AS VARCHAR(64)

SET @sSchema_Name = 'dbo'
SET @sObject_Name = 'KHKArtikel';

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 = @sSchema_Name
   OR sed.referenced_schema_name IS NULL
   )
  AND sed.referenced_entity_name = @sObject_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;