Berichten von SQL-Abhängigkeiten

Die sys.sql_expression_dependencies-Katalogsicht bietet dem Datenbankbesitzer oder dem Datenbankadministrator die Möglichkeit, Informationen über Abhängigkeiten für eine Datenbank zu ermitteln. Mithilfe dieser Sicht können Sie globale Fragen, wie etwa die folgenden beantworten:

• Welche serverübergreifenden oder datenbankübergreifenden Abhängigkeiten hat die Datenbank?

• Welche Abhängigkeiten sind in der Datenbank vorhanden?

• Welche Entitäten in der Datenbank weisen aufruferabhängige Verweise auf?

• Welche DDL-Trigger auf Serverebene oder auf Datenbankebene weisen Abhängigkeiten von Entitäten in der Datenbank auf?

• Welche Module in der Datenbank verwenden einen benutzerdefinierten Typ (UDT)?

Die Katalogsicht sys.sql_expression_dependencies hat die folgenden Einschränkungen:

• Abhängigkeiten für datenbank- und serverübergreifende Entitäten werden nur dann zurückgegeben, wenn ein gültiger drei- oder vierteiliger Name angegeben wurde. IDs für die Entitäten, auf die verwiesen wird, werden nicht zurückgegeben.

• Abhängigkeiten auf Spaltenebene werden nur für schemagebundene Entitäten gemeldet.

Die Funktion sys.dm_sql_referenced_entities gibt eine Zeile für jede benutzerdefinierte Entität zurück, auf die in der Definition der angegebenen verweisenden Entität mit ihrem Namens verwiesen wird. Die verweisende Entität kann ein benutzerdefiniertes Objekt, ein DDL-Trigger auf Serverebene oder ein DDL-Trigger auf Datenbankebene sein. Die Funktion liefert dieselben Informationen wie die sys.sql_expression_dependencies-Katalogsicht, das Resultset ist jedoch auf die Entitäten beschränkt, auf die von der angegebenen verweisenden Entität verwiesen wird. Diese Funktion ist nützlich für Entwickler, die Abhängigkeiten von Entitäten verfolgen, die sie besitzen oder für die sie über die VIEW DEFINITION-Berechtigung verfügen.

Die Funktion sys.dm_sql_referencing_entities gibt eine Zeile für jede benutzerdefinierte Entität in der aktuellen Datenbank zurück, die auf eine andere benutzerdefinierte Entität mit ihrem Namen verweist. Die verweisende Entität kann ein benutzerdefiniertes Objekt, ein benutzerdefinierter Typ (Alias oder CLR UDT), eine XML-Schemaauflistung oder eine Partitionsfunktion sein. Diese Funktion ist nützlich für Entwickler, die Abhängigkeiten von den Entitäten verfolgen möchten, die sie besitzen. Ein Entwickler kann zum Beispiel vor der Änderung eines benutzerdefinierten Typs mithilfe dieser Funktion alle Entitäten in der Datenbank ermitteln, die von diesem Typ abhängen. Beachten Sie, dass Verweise auf einen benutzerdefinierten Typ in einer Tabelle nur dann aufgezeichnet werden, wenn der Typ in der Definition einer berechneten Spalte, einer CHECK-Einschränkung oder einer DEFAULT-Einschränkung angegeben ist.

Sie können die sys.sql_expression_dependencies-Katalogsicht oder die dynamische Verwaltungsfunktion sys.dm_sql_referenced_entities abfragen, um eine Liste der Entitäten zu erhalten, von denen eine angegebene Entität abhängt. Dadurch können Sie beispielsweise eine Liste der Entitäten ermitteln, auf die ein Modul, etwa eine gespeicherte Prozedur oder ein Trigger, verweist.

Im folgenden Beispiel werden eine Tabelle, eine Sicht und drei gespeicherte Prozeduren erstellt. Diese Objekte werden in späteren Abfragen verwendet, um zu veranschaulichen, wie Abhängigkeitsinformationen berichtet werden. Beachten Sie, dass sowohl MyView als auch MyProc3 auf Mytable verweisen. MyProc1 verweist auf MyView, und MyProc2 verweist auf MyProc1.

USE AdventureWorks;
GO
-- Create entities
CREATE TABLE dbo.MyTable (c1 int, c2 varchar(32));
GO
CREATE VIEW dbo.MyView
AS SELECT c1, c2 FROM dbo.MyTable;
GO
CREATE PROC dbo.MyProc1
AS SELECT c1 FROM dbo.MyView;
GO
CREATE PROC dbo.MyProc2
AS EXEC dbo.MyProc1;
GO
CREATE PROC dbo.MyProc3
AS SELECT * FROM AdventureWorks.dbo.MyTable;
   EXEC dbo.MyProc2;
GO

Im folgenden Beispiel wird die sys.sql_expression_dependencies-Katalogsicht abgefragt, um die Entitäten zu ermitteln, auf die von MyProc3 verwiesen wird.

USE AdventureWorks;
GO
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name 
    ,referenced_server_name AS server_name
    ,referenced_database_name AS database_name
    ,referenced_schema_name AS schema_name
    , referenced_entity_name
FROM sys.sql_expression_dependencies 
WHERE referencing_id = OBJECT_ID(N'dbo.MyProc3');
GO

Dies ist das Resultset.

referencing_entity server_name database_name  schema_name referenced_entity

------------------ ----------- -------------  ----------- -----------------

MyProc3            NULL        NULL           dbo         MyProc2

MyProc3            NULL        AdventureWorks dbo         MyTable

(2 Zeile(n) betroffen)

Es werden die zwei Entitäten zurückgegeben, auf die in der Definition von MyProc3 mit ihrem Namen verwiesen wird. Der Servername ist NULL, weil die Entitäten, auf die verwiesen wird, nicht mit einem gültigen vierteiligen Namen angegeben wurden. Für MyTable wird der Datenbankname angezeigt, weil die Entität in der Prozedur mittels eines gültigen dreiteiligen Namens definiert wurde.

Ähnliche Informationen können auch mithilfe der Funktion sys.dm_sql_referenced_entities ermittelt werden. Zusätzlich zu den Objektnamen können mit der Funktion sys.dm_sql_referenced_entities Abhängigkeiten auf Spaltenebene sowohl für schemagebundene als auch für nicht schemagebundene Entitäten ermittelt werden. Im folgenden Beispiel werden die Entitäten, von denen MyProc3 abhängt, einschließlich der Abhängigkeiten auf Spaltenebene zurückgegeben.

USE AdventureWorks;
GO
SELECT referenced_server_name AS server
    , referenced_database_name AS database_name
    , referenced_schema_name AS schema_name
    , referenced_entity_name AS referenced_entity
    , referenced_minor_name AS column_name
FROM sys.dm_sql_referenced_entities ('dbo.MyProc3', 'OBJECT');
GO

Dies ist das Resultset.

server_name database_name     schema_name  referenced_entity  column_name

----------- ----------------- -----------  -----------------  -----------

NULL        NULL              dbo          MyProc2            NULL

NULL        AdventureWorks    dbo          MyTable            NULL

NULL        AdventureWorks    dbo          MyTable            c1

NULL        AdventureWorks    dbo          MyTable            c2

(4 Zeile(n) betroffen)

In diesem Resultset sind die gleichen beiden Entitäten enthalten, zusätzlich aber auch zwei weitere Zeilen, welche die Abhängigkeiten von Spalte c1 und Spalte c2 in Tabelle MyTable anzeigen. Beachten Sie, dass in der Definition MyProc3 mithilfe einer SELECT *-Anweisung auf die Spalten in der Tabelle MyTable verwiesen wird. Dies ist kein empfohlener Programmierstil; aber die Abhängigkeiten auf Spaltenebene werden immer noch von Database Engine (Datenbankmodul) verfolgt.

Die bisherigen Beispiele haben gezeigt, wie die Entitäten ermitteln werden, von denen eine Entität direkt abhängt. Das folgende Beispiel verwendet einen rekursiven allgemeinen Tabellenausdruck (CTE, Common Table Expression), um die direkten und indirekten Abhängigkeiten einer Entität zurückzugeben.

DECLARE @referencing_entity AS sysname;
SET @referencing_entity = N'MyProc3';

WITH ObjectDepends(entity_name,referenced_schema, referenced_entity, referenced_id,level)
AS (
    SELECT entity_name = 
       CASE referencing_class
          WHEN 1 THEN OBJECT_NAME(referencing_id)
          WHEN 12 THEN (SELECT t.name FROM sys.triggers AS t 
                       WHERE t.object_id = sed.referencing_id)
          WHEN 13 THEN (SELECT st.name FROM sys.server_triggers AS st
                       WHERE st.object_id = sed.referencing_id) COLLATE database_default
       END
    ,referenced_schema_name
    ,referenced_entity_name
    ,referenced_id
    ,0 AS level 
    FROM sys.sql_expression_dependencies AS sed 
    WHERE OBJECT_NAME(referencing_id) = @referencing_entity 
UNION ALL
    SELECT entity_name = 
       CASE sed.referencing_class
          WHEN 1 THEN OBJECT_NAME(sed.referencing_id)
          WHEN 12 THEN (SELECT t.name FROM sys.triggers AS t 
                       WHERE t.object_id = sed.referencing_id)
          WHEN 13 THEN (SELECT st.name FROM sys.server_triggers AS st
                       WHERE st.object_id = sed.referencing_id) COLLATE database_default
       END
    ,sed.referenced_schema_name
    ,sed.referenced_entity_name
    ,sed.referenced_id
    ,level + 1   
    FROM ObjectDepends AS o
    JOIN sys.sql_expression_dependencies AS sed ON sed.referencing_id = o.referenced_id
    )
SELECT entity_name,referenced_schema, referenced_entity, level
FROM ObjectDepends
ORDER BY level;
GO

Dies ist das Resultset.

entity_name  referenced_schema  referenced_entity  level

-----------  -----------------  -----------------  -----

MyProc3      dbo                MyProc2            0

MyProc3      dbo                MyTable            0

MyProc2      dbo                MyProc1            1

MyProc1      dbo                MyView             2

MyView       dbo                MyTable            3

(5 Zeile(n) betroffen)

In diesem Resultset werden MyProc2 und MyTable als direkte Abhängigkeiten zurückgegeben, was durch den Wert 0 für Level verdeutlicht wird. Die dritte Zeile zeigt die indirekte Abhängigkeit von MyProc1 an, auf die in der Definition von MyProc2 verwiesen wird. Die vierte Zeile zeigt die Abhängigkeit von MyView an, auf die in der Definition von MyProc1 verwiesen wird, und die letzte Zeile zeigt die Abhängigkeit von MyTable; auf diese Tabelle wird in der Definition von MyView verwiesen.

Anhand dieser hierarchisch geordneten Abhängigkeitsinformationen können Sie eine vollständige Liste aller direkten und indirekten Abhängigkeiten einer gegebenen Entität aufstellen und daraus die Reihenfolge ableiten, in der diese Objekte bereitzustellen sind, wenn sie in eine andere Datenbank verschoben werden müssen.

Im folgenden Beispiel werden die gleichen hierarchischen Abhängigkeitsinformationen mit der sys.dm_sql_referenced_entities-Funktion ermittelt. Die Entitäten, von denen MyProc3 abhängt, werden einschließlich der Abhängigkeiten auf Spaltenebene zurückgegeben.

USE AdventureWorks;
GO
DECLARE @entity AS sysname , @type AS sysname;
SET @entity = N'dbo.MyProc3';
SET @type = N'OBJECT';

WITH ObjectDepends(referenced_schema_name, referenced_entity_name, referenced_column, 
     referenced_id,level)
AS (
    SELECT 
     referenced_schema_name
    ,referenced_entity_name
    ,referenced_minor_name AS referenced_column
    ,referenced_id
    ,0 AS level 
    FROM sys.dm_sql_referenced_entities (@entity, @type)
    UNION ALL
    SELECT
     re.referenced_schema_name
    ,re.referenced_entity_name
    ,re.referenced_minor_name AS referenced_column
    ,re.referenced_id
    ,level + 1 
    FROM ObjectDepends AS o
    CROSS APPLY sys.dm_sql_referenced_entities (o.referenced_schema_name + N'.' + o.referenced_entity_name, @type) AS re
    )
SELECT referenced_schema_name, referenced_entity_name, referenced_column, level
FROM ObjectDepends
ORDER BY level;
GO

Sie können die sys.sql_expression_dependencies-Katalogsicht oder die dynamische Verwaltungsfunktion sys.dm_sql_referencing_entities verwenden, um eine Liste der Entitäten zu erhalten, die von einer angegebenen Entität abhängen. Ist beispielsweise die angegebene Entität eine Tabelle, dann werden alle Entitäten zurückgegeben, die in ihrer Definition auf diese Tabelle mit ihrem Namen verweisen.

Im folgenden Beispiel werden die Entitäten zurückgegeben, die auf die Entität dbo.MyTable verweisen.

USE AdventureWorks;
GO
SELECT OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,
    OBJECT_NAME(referencing_id) AS referencing_entity_name, 
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_column, 
    referencing_class_desc, referenced_class_desc,
    referenced_server_name, referenced_database_name, referenced_schema_name,
    referenced_entity_name, 
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column,
    is_caller_dependent, is_ambiguous
FROM sys.sql_expression_dependencies AS sed
WHERE referenced_id = OBJECT_ID(N'dbo.MyTable');
GO

Ähnliche Informationen können auch mithilfe der dynamischen Verwaltungsfunktion sys.dm_sql_referenced_entities ermittelt werden.

USE AdventureWorks;
GO
SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('dbo.MyTable', 'OBJECT');
GO

Abhängigkeiten auf Spaltenebene können mithilfe von sys.dm_sql_referenced_entities für schemagebundene und nicht schemagebundene Entitäten berichtet werden. Abhängigkeiten auf Spaltenebene für schemagebundene Entitäten können auch mit sys.sql_expression_dependencies ermittelt werden.

Im folgenden Beispiel wird sys.dm_sql_referenced_entities abgefragt, um Abhängigkeiten auf Spaltenebene von nicht schemagebundenen Entitäten zu berichten. Im folgenden Beispiel werden zuerst die Tabellen Table1 und Table 2 sowie die gespeicherte Prozedur Proc1 erstellt. Die Prozedur verweist auf die Spalten b und c in Tabelle Table1 und auf die Spalte c2 in Tabelle Table2. Die Sicht sys.dm_sql_referenced_entities wird mit der gespeicherten Prozedur ausgeführt, die als verweisende Entität angegeben ist. Das Resultset enthält Zeilen für die Entitäten Table1 und Table2, auf die verwiesen wird, und die Spalten, auf die in der Definition der gespeicherten Prozedur verwiesen wird. Beachten Sie, dass in der column_name-Spalte NULL in den Zeilen zurückgegeben wird, die auf die Tabellen verweisen.

USE AdventureWorks;
GO
CREATE TABLE dbo.Table1 (a int, b int, c int);
GO
CREATE TABLE dbo.Table2 (c1 int, c2 int);
GO
CREATE PROCEDURE dbo.Proc1 AS
    SELECT b, c FROM dbo.Table1;
    SELECT c2 FROM dbo.Table2;
GO
SELECT referenced_id, referenced_entity_name AS table_name, referenced_minor_name AS column_name
FROM sys.dm_sql_referenced_entities ('dbo.Proc1', 'OBJECT');
GO

Dies ist das Resultset.

referenced_id, table_name,  column_name

-------------  -----------  -------------

151671588      Table1       NULL

151671588      Table1       b

151671588      Table1       c

2707154552     Table2       NULL

2707154552     Table2       c2

Eine datenbankübergreifende Abhängigkeit entsteht dadurch, dass eine Entität mithilfe eines gültigen dreiteiligen Namens auf eine andere Entität verweist. Eine serverübergreifende Abhängigkeit entsteht, wenn eine Entität mithilfe eines gültigen vierteiligen Namens auf eine andere Entität verweist. Der Name des Servers und der Datenbank werden nur aufgezeichnet, wenn der Name explizit angegeben wird. Wird beispielsweise MyServer.MyDB.MySchema.MyTable angegeben, werden der Name des Servers und der Datenbank aufgezeichnet; wird dagegen MyServer..MySchema.MyTableangegeben, wird nur der Name des Servers aufgezeichnet. Weitere Informationen darüber, wie serverübergreifende und datenbankübergreifende Abhängigkeiten verfolgt werden, finden Sie unter Grundlegendes zu SQL-Abhängigkeiten.

Datenbankübergreifende und serverübergreifende Abhängigkeiten können mithilfe der Funktionen sys.sql_expression_dependencies oder sys.dm_sql_referenced_entitites berichtet werden.

Im folgenden Beispiel werden alle datenbankübergreifenden Abhängigkeiten zurückgegeben. Im Beispiel werden zuerst die Datenbank db1 sowie zwei gespeicherte Prozeduren erstellt, die auf Tabellen in den Datenbanken db2 und db3 verweisen. Die Tabelle sys.sql_expression_dependencies wird dann abgefragt, um die datenbankübergreifenden Abhängigkeiten zwischen den Prozeduren und den Tabellen zu berichten. Beachten Sie, dass für die Entität t3, auf die verweisen wird, in der Spalte referenced_schema_name NULL angegeben ist, weil in der Definition der Prozedur kein Schemaname für diese Entität angegeben wurde.

CREATE DATABASE db1;
GO
USE db1;
GO
CREATE PROCEDURE p1 AS SELECT * FROM db2.s1.t1;
GO
CREATE PROCEDURE p2 AS
    UPDATE db3..t3
    SET c1 = c1 + 1;
GO
SELECT OBJECT_NAME (referencing_id),referenced_database_name, 
    referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL;
GO
USE master;
GO
DROP DATABASE db1;
GO

Ein aufruferabhängiger Verweis bedeutet, dass die Schemabindung für die Entität, auf die verwiesen wird, zur Laufzeit erfolgt. Demzufolge hängt die Auflösung der Entitäts-ID vom Standardschema des Aufrufers ab. Dieser Vorgang wird dynamische Schemabindung genannt. Er tritt auf, wenn die Entität, auf die verwiesen wird, eine gespeicherte Prozedur, eine erweiterte gespeicherte Prozedur oder eine nicht schemagebundene benutzerdefinierte Funktion ist, die in einer EXECUTE-Anweisung ohne Angabe eines Schemanamens aufgerufen wird. Ein Verweis auf eine Entität im Format EXECUTE MySchema.MyProc ist zum Beispiel nicht aufruferabhängig, ein Verweis im Format EXECUTE MyProc ist dagegen aufruferabhängig.

Aufruferabhängige Verweise führen möglicherweise zu unerwartetem Verhalten, wenn das Modul, in dem darauf verwiesen wird, ausgeführt wird. Ziehen Sie als Beispiel die folgende gespeicherte Prozedur in Betracht, die mithilfe eines einteiligen Namens auf eine Prozedur verweist.

CREATE PROCEDURE dbo.Proc1
AS EXECUTE dbo.Proc2;

Bei der Ausführung von Proc1 wird Proc2 an das Schema des Aufrufers gebunden. Angenommen, Proc1 wird von User1 mit dem Standardschema S1 und von User2 mit dem Standardschema S2 ausgeführt. Bei der Ausführung von Proc1 durch User1 wird die Entität, auf die verwiesen wird, zu S1.Proc2 aufgelöst. Bei der Ausführung von Proc1 durch User2 wird die Entität, auf die verwiesen wird, zu S2.Proc2 aufgelöst. Wegen dieses Verhaltens kann die ID von Proc2 erst nach der Ausführung von Proc1 aufgelöst werden, weshalb die Spalte is_caller_dependent in der sys.sql_expression_dependencies-Sicht und der sys.dm_sql_referenced_entities-Funktion auf 1 gesetzt wird. Nach der Ausführung von Proc1 sucht Database Engine (Datenbankmodul) im Standardschema des Aufrufers nach der Entität Proc2, auf die verwiesen wird. Wird sie nicht gefunden, wird das DBO-Schema überprüft. Wenn Proc2 auch im DBO-Schema nicht gefunden wird, kann die ID von Proc2 nicht aufgelöst werden, und die Anweisung schlägt fehl. Wir empfehlen, Verweise auf Datenbankentitäten mithilfe zweiteiliger Namen anzugeben, um potenzielle Anwendungsfehler auszuschließen.

Im folgenden Beispiel wird jede Entität in der aktuellen Datenbank zurückgegeben, die einen aufruferabhängigen Verweis enthält.

SELECT OBJECT_NAME(referencing_id) AS referencing_entity, referenced_database_name, 
    referenced_schema_name, referenced_entity_name, referenced_id 
FROM sys.sql_expression_dependencies
WHERE is_caller_dependent = 1;

Im folgenden Beispiel wird jede Entität in der aktuellen Datenbank zurückgegeben, die in ihrer Definition auf den angegebenen Typ verweist. Das Resultset zeigt an, dass zwei gespeicherte Prozeduren diesen Typ verwenden. Der Typ wird auch in der Definition mehrerer Spalten der Tabelle HumanResources.Employee verwendet; da jedoch der Typ nicht in der Definition einer berechneten Spalte, einer CHECK-Einschränkung oder einer DEFAULT-Einschränkung der Tabelle enthalten ist, werden keine Zeilen für die Tabelle zurückgegeben.

USE AdventureWorks;
GO
SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('dbo.Flag', 'TYPE');
GO

Abhängigkeiten von DDL-Triggern auf Serverebene können mit den Funktionen sys.sql_expression_dependencies und sys.dm_sql_referenced_entitites nur dann berichtet werden, wenn der Kontext auf die master-Datenbank festgelegt ist. Wird die sys.dm_sql_referenced_entities-Funktion verwendet, kann der Kontext jede Datenbank sein.

Im folgenden Beispiel wird die sys.sql_expression_dependencies-Sicht abgefragt, um über DDL-Trigger-Abhängigkeiten auf Serverebene zu berichten.

USE master;
GO
SELECT OBJECT_NAME(referencing_id) AS referencing_entity, referencing_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, referenced_id
FROM sys.sql_expression_dependencies
WHERE referencing_class = 13;