Report sulle dipendenze SQL

La vista del catalogo sys.sql_expression_dependencies indica il proprietario del database o l'amministratore del database che può creare report sulle informazioni di dipendenza per un determinato database. Questa vista consente di rispondere a domande globali, ad esempio:

• Quali sono le dipendenze tra server o tra database?

• Quali dipendenze esistono nel database?

• Quali entità del database hanno riferimenti dipendenti dal chiamante?

• Quali trigger DDL a livello di server o a livello di database hanno dipendenze sulle entità nel database?

• Quali moduli nel database utilizzano un tipo definito dall'utente?

sys.sql_expression_dependencies ha le limitazioni seguenti:

• Le dipendenze sulle entità tra server e tra database vengono restituite solo quando viene specificato un nome valido composto da quattro parti o da tre parti. Gli ID per le entità a cui si fa riferimento non vengono restituiti.

• Solo per le entità associate a schemi vengono creati report sulle dipendenze a livello di colonna.

La funzione sys.dm_sql_referenced_entities restituisce una riga per ogni entità definita dall'utente a cui viene fatto riferimento in base al nome nella definizione dell'entità di riferimento specificata. L'entità di riferimento può essere un oggetto definito dall'utente, un trigger DDL a livello di server o un trigger DDL a livello di database. Si tratta delle stesse informazioni restituite da sys.sql_expression_dependencies, tuttavia il set di risultati è limitato alle entità alle quali fa riferimento l'entità di riferimento specificata. Questa funzione è utile per gli sviluppatori che desiderano rilevare le dipendenze nei propri moduli o che dispongono dell'autorizzazione VIEW DEFINITION.

La funzione sys.dm_sql_referencing_entities restituisce una riga per ogni entità definita dall'utente nel database corrente che fa riferimento a un'altra entità definita dall'utente in base al nome. L'entità di riferimento può essere un oggetto definito dall'utente, un tipo (alias o CLR definito dall'utente), una raccolta XML Schema o una funzione di partizione. Questa funzione è utile per gli sviluppatori che desiderano rilevare dipendenze relative alle proprie entità. Ad esempio, prima di modificare un tipo definito dall'utente, uno sviluppatore può utilizzare questa funzione per determinare tutte le entità del database che dipendono da quel tipo. Si noti che non vengono creati report sui riferimenti a un tipo definito dall'utente di una tabella a meno che il tipo non sia specificato nella definizione di una colonna calcolata, in un vincolo CHECK o in un vincolo DEFAULT.

È possibile eseguire query nella vista catalogo sys.sql_expression_dependencies o nella funzione a gestione dinamica sys.dm_sql_referenced_entities per visualizzare un elenco di entità da cui dipende un'entità specificata. Ad esempio, è possibile restituire un elenco di entità a cui fa riferimento un modulo quale una stored procedure o un trigger.

Nell'esempio seguente sono create una tabella, una vista e tre stored procedure. Questi oggetti sono utilizzati nelle query successive per dimostrare come creare report sulle informazioni di dipendenza. Si noti che MyView e MyProc3 fanno riferimento a Mytable. MyProc1 fa riferimento a MyView e MyProc2 fa riferimento a MyProc1.

USE AdventureWorks2008R2;
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 AdventureWorks2008R2.dbo.MyTable;
   EXEC dbo.MyProc2;
GO

Nell'esempio seguente viene eseguita una query nella vista del catalogo di sys.sql_expression_dependencies per visualizzare le entità alle quali fa riferimento MyProc3.

USE AdventureWorks2008R2;
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

Set di risultati:

referencing_entity server_name database_name         schema_name referenced_entity

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

MyProc3            NULL        NULL                  dbo         MyProc2

MyProc3            NULL        AdventureWorks2008R2  dbo         MyTable

(2 righe interessate)

Vengono restituite le due entità a cui fa riferimento il nome della definizione di MyProc3. Il nome del server è NULL perché le entità a cui si fa riferimento non vengono specificate utilizzando un nome valido composto da quattro parti. Per MyTable viene visualizzato il nome del database poiché l'entità è stata definita nella procedura utilizzando un nome valido composto da tre parti.

Informazioni simili possono essere restituite utilizzando sys.dm_sql_referenced_entities. Questa funzione viene usata per i report sui nomi oggetto e può essere utilizzata per restituire dipendenze a livello di colonna relative a entità associate a schemi e non. Nell'esempio seguente sono restituite le entità dalle quali dipende MyProc3, incluse le dipendenze a livello di colonna.

USE AdventureWorks2008R2;
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

Set di risultati:

server_name database_name         schema_name  referenced_entity  column_name

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

NULL        NULL                  dbo          MyProc2            NULL

NULL        AdventureWorks2008R2  dbo          MyTable            NULL

NULL        AdventureWorks2008R2  dbo          MyTable            c1

NULL        AdventureWorks2008R2  dbo          MyTable            c2

(4 righe interessate)

In questo set di risultati, vengono restituite le due stesse entità, tuttavia vengono restituite due righe aggiuntive in cui vengono visualizzate le dipendenze relative alle colonne c1 e c2 di MyTable. Si noti che nella definizione di MyProc3, è stata utilizzata un'istruzione SELECT * per fare riferimento alle colonne in MyTable. Non si tratta di una procedura di codifica raccomandata, tuttavia le dipendenze a livello di colonna vengono ancora rilevate da Motore di database.

Gli esempi fino a ora riportati hanno illustrato come restituire le entità dalle quali dipende direttamente un'entità. Nell'esempio seguente viene utilizzata un'espressione di tabella comune ricorsiva (CTE) al fine di restituire tutte le dipendenze dirette e indirette di un'entità.

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

Set di risultati:

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 righe interessate)

In questo set di risultati, MyProc2 e MyTable sono restituiti come dipendenze dirette, come indicato dal valore del livello 0. La terza riga indica la dipendenza indiretta relativa a MyProc1 a cui si fa riferimento nella definizione di MyProc2. Nella quarta riga viene visualizzata la dipendenza relativa a MyView a cui si fa riferimento nella definizione di MyProc1 e, infine, una dipendenza relativa a MyTable a cui si fa riferimento nella definizione di MyView.

Con la restituzione di informazioni di dipendenza gerarchica, è possibile determinare l'elenco completo di dipendenze dirette e indirette relativo a una determinata entità e ottenere l'ordine di distribuzione di questi oggetti nel caso debbano essere spostati in un altro database.

Nell'esempio seguente vengono restituite le stesse informazioni di dipendenza gerarchica utilizzando la funzione sys.dm_sql_referenced_entities. Vengono restituite le entità da cui dipende MyProc3, incluse le dipendenze a livello di colonna.

USE AdventureWorks2008R2;
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

È possibile eseguire query nella vista del catalogo sys.sql_expression_dependencies o nella funzione a gestione dinamica sys.dm_sql_referencing_entities per visualizzare un elenco di entità che dipendono dall'entità specificata. Ad esempio, se l'entità specificata è una tabella, vengono restituite tutte le entità che fanno riferimento al nome della tabella nella propria definizione.

Nell'esempio seguente vengono restituite le entità che fanno riferimento all'entità dbo.MyTable.

USE AdventureWorks2008R2;
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

Informazioni simili possono essere restituite utilizzando la funzione a gestione dinamica sys.dm_sql_referenced_entities.

USE AdventureWorks2008R2;
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

È possibile creare report sulle dipendenze a livello di colonna utilizzando sys.dm_sql_referenced_entities sia per le entità associate a schemi, sia per le entità non associate a schemi. È anche possibile creare report sulle dipendenze a livello di colonna relative a entità associate a schemi utilizzando sys.sql_expression_dependencies.

Nell'esempio seguente vengono eseguite query su sys.dm_sql_referenced_entities per creare report sulle dipendenze a livello di colonna relative alle entità non associate a schemi. Nell'esempio vengono create Table1 e Table 2 e la stored procedure Proc1. La procedura fa riferimento alle colonne b e c in Table1 e alla colonna c2 in Table2. La vista sys.dm_sql_referenced_entities viene eseguita con la stored procedure indicata come entità di riferimento. Il set di risultati contiene le righe per le entità Table1 e Table2 a cui viene fatto riferimento e le colonne a cui viene fatto riferimento nella definizione della stored procedure. Si noti che viene restituito NULL nella colonna column_name nelle righe che fanno riferimento alle tabelle.

USE AdventureWorks2008R2;
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

Set di risultati:

referenced_id, table_name,  column_name

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

151671588      Table1       NULL

151671588      Table1       b

151671588      Table1       c

2707154552     Table2       NULL

2707154552     Table2       c2

Una dipendenza tra database viene creata quando un'entità fa riferimento a un'altra entità utilizzando un nome valido composto da tre parti. Una dipendenza tra server viene creata quando un'entità fa riferimento a un'altra entità utilizzando un nome valido composto da quattro parti. Il nome del server e il database vengono registrati solo quando il nome viene specificato in modo esplicito. Ad esempio, quando si specifica MyServer.MyDB.MySchema.MyTable viene registrato il nome del server e del database; tuttavia, quando viene specificato MyServer..MySchema.MyTable, viene registrato solo il nome del server. Per ulteriori informazioni sulle modalità di rilevamento delle dipendenze tra server e tra database, vedere Informazioni sulle dipendenze SQL.

È possibile creare report sulle dipendenze tra database e tra server utilizzando sys.sql_expression_dependencies o sys.dm_sql_referenced_entitites.

Nell'esempio seguente vengono restituite tutte le dipendenze tra database. L'esempio prima crea il database db1 e due stored procedure che fanno riferimento alle tabelle nei database db2 e db3. Sulla tabella sys.sql_expression_dependencies viene quindi eseguita una query per riportare le dipendenze tra database tra le procedure e le tabelle. Notare che NULL è restituito nella colonna referenced_schema_name per l'entità t3 a cui si fa riferimento perché per l'entità non è stato specificato un nome nella definizione della procedura.

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

Un riferimento dipendente dal chiamante indica che l'associazione allo schema dell'entità con riferimenti si verifica in fase di esecuzione; pertanto, la risoluzione dell'ID dell'entità dipende dallo schema predefinito del chiamante. Questo è comunemente chiamato associazione di schema dinamica e si verifica quando l'entità a cui si fa riferimento è una stored procedure, una stored procedure estesa o una funzione definita dall'utente non associata a schemi in un'istruzione EXECUTE, senza specificare un nome dello schema. Ad esempio, un riferimento a un'entità in formato EXECUTE MySchema.MyProc non è dipendente dal chiamante; tuttavia un riferimento in formato EXECUTE MyProc è dipendente dal chiamante.

I riferimenti dipendenti dal chiamante possono provocare un comportamento imprevisto quando viene eseguito un modulo in cui vi si fa riferimento. Si consideri, ad esempio, la seguente stored procedure che fa riferimento a una procedura utilizzando un nome composto da una parte.

CREATE PROCEDURE dbo.Proc1
AS EXECUTE dbo.Proc2;
GO

Quando si esegue Proc1, Proc2 si associa allo schema del chiamante. Si supponga che Proc1 venga eseguito da User1 che ha uno schema predefinito di S1 che viene eseguito da User2 che ha uno schema predefinito di S2. Quando Proc1 viene eseguito da User1, l'entità a cui si fa riferimento viene risolta in S1.Proc2. Quando Proc1 viene eseguito da User2, l'entità a cui si fa riferimento viene risolta in S2.Proc2. A causa di questo comportamento, l'ID di Proc2 non può essere risolto, fino all'esecuzione di Proc1; pertanto la colonna is_caller_dependent è impostata su 1 nella vista sys.sql_expression_dependencies e nella funzione sys.dm_sql_referenced_entities. Quando Proc1 è eseguito, il Motore di database cercherà l'entità Proc2 a cui si fa riferimento nello schema predefinito del chiamante. Se non viene trovato, viene controllato lo schema dbo. Se Proc2 non è nello schema dbo, l'ID di Proc2 non può essere risolto e si verificano errori nell'istruzione. Si consiglia di specificare nomi composti da due parti quando si fa riferimento alle entità del database per eliminare potenziali errori dell'applicazione.

Nell'esempio seguente viene restituita ogni entità nel database corrente contenente un riferimento dipendente dal chiamante.

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;

Nell'esempio seguente viene restituita ogni entità nel database corrente che fa riferimento al tipo specificato nella definizione. Il set di risultati mostra che questo tipo è utilizzato da due stored procedure. Il tipo viene anche utilizzato nella definizione di diverse colonne della tabella HumanResources.Employee. Tuttavia, poiché il tipo non è presente nella definizione di una colonna calcolata o a causa di un vincolo CHECK o DEFAULT della tabella, non viene restituita alcuna riga.

USE AdventureWorks2008R2;
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

È possibile creare un report delle dipendenze di trigger DDL a livello di server utilizzando sys.sql_expression_dependencies e sys.dm_sql_referencing_entities solo quando il contesto è impostato sul database master. Se si utilizza la funzione sys.dm_sql_referenced_entities, il contesto può essere qualsiasi database.

Nell'esempio seguente viene eseguita una query sulla vista sys.sql_expression_dependencies per creare un report sulle dipendenze di un trigger DDL a livello di server.

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;