Informando dependências de SQL

A exibição de catálogo sys.sql_expression_dependencies oferece ao proprietário ou administrador do banco de dados a habilidade de relatar informações de dependência de determinado banco de dados. Usando essa exibição, você poderá responder perguntas globais, como as seguintes:

• Quais são as dependências existentes entre servidores ou entre banco de dados que o banco de dados possui?

• Que dependências existem dentro do banco de dados?

• Quais entidades do banco de dados possuem referências dependentes de chamador?

• Que disparadores DDL em nível de servidor ou de banco de dados têm dependências de entidades no banco de dados?

• Quais módulos do banco de dados usam um tipo definido pelo usuário (UDT)?

A sys.sql_expression_dependencies tem as seguintes limitações:

• Só são retornadas dependências de entidades entre servidores e entre bancos de dados quando um nome válido de quatro ou três partes é especificado. Não são retornadas IDs para entidades referenciadas.

• Só são relatadas dependências em nível de coluna de entidades associadas a esquema.

A função sys.dm_sql_referenced_entities retorna uma linha para cada entidade definida pelo usuário e referenciada por nome na definição da entidade de referência especificada. A entidade de referência pode ser um objeto definido pelo usuário, disparador DDL em nível de servidor ou disparador DDL em nível de banco de dados. Essa é a mesma informação retornada por sys.sql_expression_dependencies. No entanto, o conjunto de resultados limita-se às entidades que são referenciadas pela entidade de referência especificada. Essa função é útil para desenvolvedores que queiram controlar dependências em seus próprios módulos, ou em que tenham permissão VIEW DEFINITION.

A função sys.dm_sql_referencing_entities retorna uma linha para cada uma das entidades definidas pelo usuário no banco de dados atual, que referenciam outras entidades definidas pelo usuário por nome. A entidade de referência pode ser um objeto definido pelo usuário, tipo (alias ou CLR UDT), coleção de esquema XML ou função de partição. Essa função é útil para os desenvolvedores controlarem dependências nas entidades que eles possuem. Por exemplo, antes de modificar um tipo definido pelo usuário, um desenvolvedor pode usar a função para determinar todas as entidades no banco de dados que dependem desse tipo. Observe que não são relatadas referências a um tipo definido pelo usuário em uma tabela, a menos que o tipo seja especificado na definição de uma coluna computada, restrição CHECK ou restrição DEFAULT.

Você pode consultar a exibição de catálogo sys.sql_expression_dependencies ou a função de gerenciamento dinâmico sys.dm_sql_referenced_entities para retornar uma lista de entidades da qual depende a entidade especificada. Por exemplo, é possível retornar uma lista de entidades que são referenciadas por um módulo, como um procedimento armazenado ou disparador.

O exemplo a seguir cria uma tabela, uma exibição e três procedimentos armazenados. Esses objetos são usados em consultas posteriores para demonstrar como relatar informações de dependência. Observe que MyView e MyProc3 referenciam Mytable. MyProc1 referencia MyView e MyProc2 referencia 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

O exemplo a seguir consulta a exibição de catálogo sys.sql_expression_dependencies para retornar as entidades referenciadas por 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

Aqui está o conjunto de resultados.

referencing_entity server_name database_name         schema_name referenced_entity

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

MyProc3            NULL        NULL                  dbo         MyProc2

MyProc3            NULL        AdventureWorks2008R2  dbo         MyTable

(2 linha(s) afetada(s))

As duas entidades referenciadas por nome na definição de MyProc3 são retornadas. O nome do servidor é NULL porque as entidades de referência não são especificadas usando nome válido de quatro partes. O nome de banco de dados é mostrado para MyTable porque a entidade foi definida no procedimento como usando um nome válido de três partes.

Informações similares podem ser retornadas por meio de sys.dm_sql_referenced_entities. Além de relatar nomes de objeto, essa função pode ser usada para retornar dependências em nível de coluna tanto em entidades associadas a esquema como em entidades não associadas a esquema. O exemplo a seguir retorna as entidades das quais MyProc3 depende, inclusive dependências em nível de coluna.

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

Aqui está o conjunto de resultados.

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 linha(s) afetada(s))

Nesse conjunto de resultados, as mesmas duas entidades são retornadas. Contudo, duas linhas adicionais são retornadas, mostrando a dependência das colunas c1 e c2 em MyTable. Observe que na definição de MyProc3, foi usada uma instrução SELECT * para referenciar as colunas de MyTable. Essa não é uma prática de codificação recomendada; porém, as dependências em nível de coluna ainda são controladas pelo Mecanismo de Banco de Dados.

Até esse momento, os exemplos ilustraram como retornar as entidades das quais uma entidade depende diretamente. O exemplo a seguir usa uma expressão de tabela comum recursiva (CTE) para retornar todas as dependências diretas e indiretas de uma entidade.

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

Aqui está o conjunto de resultados.

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 linhas afetadas)

Nesse conjunto de resultados, são retornados MyProc2 e MyTable como dependências diretas, conforme indicado pelo valor de nível 0. A terceira linha mostra a dependência indireta em MyProc1, referenciada na definição de MyProc2. A quarta linha mostra a dependência em MyView, referenciada na definição de MyProc1 e, finalmente, uma dependência em MyTable, referenciada na definição de MyView.

Com o retorno de informações de dependência hierárquica, você pode determinar a lista completa de dependências diretas e indiretas de uma entidade específica, e inferir a ordem de implementação desses objetos, caso necessitem ser movidos para outro banco de dados.

O exemplo a seguir retorna as mesmas informações de dependência hierárquica, usando a função sys.dm_sql_referenced_entities. As entidades das quais MyProc3 depende são retornadas com a inclusão de dependências em nível de coluna.

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

Você pode consultar a exibição de catálogo sys.sql_expression_dependencies ou a função de gerenciamento dinâmico sys.dm_sql_referencing_entities para retornar uma lista de entidades que dependem da entidade especificada. Por exemplo, se a entidade especificada for uma tabela, todas as entidades que referenciam essa tabela por nome em suas definições serão retornadas.

O exemplo a seguir retorna as entidades que referenciam a entidade 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

Informações similares podem ser retornadas usando a função de gerenciamento dinâmico 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

As dependências em nível de coluna podem ser relatadas usando sys.dm_sql_referenced_entities, tanto para entidades associadas a esquema como para entidades não associadas a esquema. As dependências em nível de coluna em entidades associadas a esquema também podem ser relatadas usando sys.sql_expression_dependencies.

O exemplo a seguir consulta sys.dm_sql_referenced_entities para relatar dependências em nível de coluna em entidades não associadas a esquema. Primeiro o exemplo cria Table1 e Table 2 e o procedimento armazenado Proc1. O procedimento faz referência às colunas b e c de Table1 à coluna c2 de Table2. A exibição sys.dm_sql_referenced_entities é executada com o procedimento armazenado especificado como entidade de referência. O conjunto de resultados contém linhas das entidades referenciadas Table1 e Table2 e as colunas que são referenciadas na definição do procedimento armazenado. Observe que NULL é retornado na coluna column_name nas linhas que fazem referência às tabelas.

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

Aqui está o conjunto de resultados.

referenced_id, table_name,  column_name

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

151671588      Table1       NULL

151671588      Table1       b

151671588      Table1       c

2707154552     Table2       NULL

2707154552     Table2       c2

Uma dependência entre banco de dados é criada quando uma entidade faz referência a outra entidade usando um nome válido de três partes. Uma referência entre servidores é criada quando uma entidade faz referência a outra entidade usando um nome válido de quatro partes. Os nomes do servidor e do banco de dados só são registrados quando o nome é explicitamente especificado. Por exemplo, quando especificados como MyServer.MyDB.MySchema.MyTable, os nomes de servidor e de banco de dados são registrados; porém, quando especificados como MyServer..MySchema.MyTable, só o nome de servidor é registrado. Para obter informações sobre como são controladas as dependências entre servidor e entre banco de dados, consulte Compreendendo dependências do SQL.

As dependências entre banco de dados e entre servidores podem ser relatadas, usando sys.sql_expression_dependencies ou sys.dm_sql_referenced_entitites.

O exemplo a seguir retorna todas as dependências entre banco de dados. O exemplo cria primeiramente um banco de dados db1 e dois procedimentos armazenados que referenciam tabelas dos bancos de dados db2 e db3. A tabela sys.sql_expression_dependencies é consultada em seguida, para relatar as dependências entre bancos de dados existentes entre procedimentos e tabelas. Observe que NULL é retornado na coluna referenced_schema_name para a entidade referenciada t3 porque o nome de esquema daquela entidade não foi especificado na definição do procedimento.

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

Uma referência dependente de chamador significa que o esquema de associação da entidade referenciada ocorre em tempo de execução. Dessa forma, a resolução da ID de entidade depende do esquema padrão do chamador. Em geral, isso é denominado associação de esquema dinâmico e acontece quando a entidade referenciada é um procedimento armazenado; um procedimento armazenado estendido ou uma função definida pelo usuário, não associada a esquema, chamada em uma instrução EXECUTE sem especificação de nome de esquema. Por exemplo, uma referência a uma entidade no formato EXECUTE MySchema.MyProc não é dependente de chamador; porém, uma referência no formato EXECUTE MyProc é dependente de chamador.

As referências dependentes de chamador podem provocar comportamento inesperado quando o módulo em que são referenciadas é executado. Por exemplo, considere o procedimento armazenado a seguir, que referencia um procedimento usando nome de uma parte.

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

Quando o Proc1 é executado, o Proc2 associa-se ao esquema do chamador. Suponha que o Proc1 seja executado pelo User1, que possui um esquema padrão S1 e executado pelo User2, que possui um esquema padrão S2. Quando o Proc1 for executado pelo User1, a entidade referenciada será resolvida em S1.Proc2. Quando o Proc1 for executado pelo User2, a entidade referenciada será resolvida em S2.Proc2. Por causa desse comportamento, a ID do Proc2 não pode ser resolvida até que o Proc1 seja executado; por isso, a coluna is_caller_dependent é definida como 1 na exibição sys.sql_expression_dependencies e na função sys.dm_sql_referenced_entities. Quando o Proc1 for executado, o Mecanismo de Banco de Dados procurará a entidade referenciada Proc2 no esquema padrão do chamador. Não sendo localizado, o esquema dbo é verificado. Se o Proc2 não for localizado no esquema dbo, a ID do Proc2 não poderá ser resolvido, e as instruções falharão. Recomendamos especificar nomes de duas partes em referências a entidades de banco de dados para eliminar possíveis erros de aplicativo.

O exemplo a seguir retorna cada entidade do atual banco de dados que contenha uma referência dependente de chamador.

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;

O exemplo a seguir retorna cada entidade do atual banco de dados que referencia um tipo especificado em sua definição. O conjunto de resultados mostra que dois procedimentos armazenados usam esse tipo. O tipo é também usado na definição de várias colunas da tabela HumanResources.Employee; porém, como o tipo não consta da definição de uma coluna computada, da restrição CHECK, ou da restrição DEFAULT da tabela, nenhuma linha é retornada para a tabela.

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

Você pode relatar sobre dependências de gatilhos DDL em nível de servidor usando sys.sql_expression_dependencies e sys.dm_sql_referencing_entities apenas quando o contexto estiver definido como banco de dados mestre. Ao usar a função sys.dm_sql_referenced_entities, o contexto pode ser qualquer banco de dados.

O exemplo a seguir consulta a exibição sys.sql_expression_dependencies para relatar as dependências de disparador DDL em nível de servidor.

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;