Condividi tramite


Report sulle dipendenze SQL

Le dipendenze SQL sono i riferimenti in base al nome utilizzati nelle espressioni SQL per creare dipendenze tra un'entità definita dall'utente e un'altra entità. Le viste e le stored procedure dipendono ad esempio dall'esistenza di tabelle che contengono i dati restituiti dalla vista o dalla procedura. Creare report sulle informazioni di dipendenza è utile negli scenari seguenti:

  • Spostamento di un modulo, ad esempio una stored procedure, da un'applicazione a un'altra.

    Prima di spostare un modulo, è possibile determinare se esiste un qualsiasi database o entità tra database referenziati dal modulo che deve anche essere spostato insieme al modulo stesso.

  • Modifica della definizione di un'entità, ad esempio l'aggiunta o l'eliminazione di una colonna in una tabella.

    Prima di modificare un'entità, è possibile determinare se ci sono altre entità che dipendono dalla definizione corrente dell'entità stessa. Queste entità dipendenti possono produrre risultati imprevisti quando viene eseguita su di loro una query o una chiamata dopo la loro modifica e possono richiedere un'operazione di aggiornamento di metadati o una modifica alle loro definizioni.

  • Muovere uno o più database da un server a un altro.

    Prima di spostare database ad un altro server, è possibile determinare se le entità in un database hanno dipendenze rispetto alle entità di un altro database. In tal modo si saprà che occorre spostare quei database sullo stesso server.

  • Configurare failover per applicazioni che si estendono su più database.

    Si vuole assicurare che l'applicazione sia sempre disponibile e utilizzi il mirroring del database come strategia di failover. L'applicazione dipende da più di un database e si desidera assicurarsi che l'applicazione possa eseguirlo correttamente se si verificano errori sul server mirror. Con il mirroring a livello di database, è necessario determinare quali siano i database essenziali per l'applicazione, in modo da impostare il mirroring per ogni singolo database. È quindi possibile assicurarsi che si verifichi un failover su tutti i database, garantendo in tal modo che l'applicazione funzioni sul server mirror.

  • Cercare entità in un'applicazione che esegue query distribuite utilizzando nomi composti da quattro parti.

    Si desidera sapere quali sono i server collegati che vengono utilizzati nelle query distribuite.

  • Cercare entità utilizzate in un'applicazione che contiene riferimenti dipendenti dal chiamante o riferimenti del nome composto da una parte.

    Prima di distribuire un'applicazione, è possibile determinare se entità utilizzate dall'applicazione contengono riferimenti dipendenti dal chiamante o fanno riferimento a entità utilizzando solo un nome composto da una parte. Tali riferimenti indicano procedure di programmazione scarse ed è possibile che si verifichino comportamenti imprevisti durante la distribuzione dell'applicazione. Questo avviene perché la risoluzione (associazione) dell'entità a cui si fa riferimento dipende dallo schema del chiamante e questa informazione viene determinata solo in fase di esecuzione. Dopo avere trovato questi riferimenti, le query possono essere corrette specificando il nome appropriato composto da più parti, ad esempio schema_name.object_name.

Per ulteriori informazioni sulle dipendenze SQL, vedere Informazioni sulle dipendenze SQL.

Report sulle dipendenze utilizzando viste e funzioni di sistema

Per visualizzare le dipendenze SQL, SQL Server 2008 fornisce la vista del catalogo sys.sql_expression_dependencies e le funzioni a gestione dinamica sys.dm_sql_referenced_entities e sys.dm_sql_referencing_entities. È possibile eseguire una query su questi oggetti per visualizzare le informazioni di dipendenza sulle entità definite dall'utente.

Le dipendenze SQL possono essere visualizzate anche utilizzando Visualizza dipendenze in SQL Server Management Studio. Per ulteriori informazioni, vedere Procedura: Visualizzazione delle dipendenze SQL (SQL Server Management Studio).

Utilizzo della vista del catalogo sys.sql_expression_dependencies

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.

Utilizzo della funzione a gestione dinamica sys.dm_sql_referenced_entities

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.

Utilizzo della funzione a gestione dinamica sys.dm_sql_referencing_entities

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.

Esempi

I seguenti esempi restituiscono dipendenze SQL utilizzando la vista del catalogo sys.sql_expression_dependencies e le funzioni a gestione dinamica sys.dm_sql_referenced_entities e sys.dm_sql_referencing_entities.

Report sulle entità dalle quali dipende un'entità specificata

È 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

Report sulle entità che dipendono da un'entità specificata

È 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

Report sulle dipendenze a livello di colonna

È 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

Report sulle dipendenze tra server e tra database

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

Report sui riferimenti dipendenti dal chiamante

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;

Report sulle entità che utilizzano un determinato tipo definito dall'utente

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

Report sulle dipendenze di un trigger DDL a livello di server

È 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;