Informando dependências de SQL
As dependências de SQL são referências por nome usadas em expressões SQL que tornam uma entidade definida pelo usuário dependente de outra entidade. Por exemplo, exibições e procedimentos armazenados dependem da existência de tabelas que contenham os dados retornados pela exibição ou pelo procedimento. O relatório das informações de dependência é útil nos seguintes cenários:
Movendo um módulo, como procedimento armazenado, de um aplicativo para outro.
Antes de mover um módulo, determine se há entidades em algum banco de dados ou em todos os bancos de dados referenciados pelo módulo que também devam ser movidos juntamente com o módulo.
Modificando a definição de uma entidade, por exemplo, adicionando ou descartando uma coluna de tabela.
Antes de modificar uma entidade, determine se há outras entidades dependentes da sua definição atual. As entidades dependentes podem produzir resultados inesperados quando consultadas ou invocadas após a modificação, e podem requerer operação de atualização de metadados ou modificação de suas definições.
Movendo um ou mais bancos de dados de um servidor para outro.
Antes de mover bancos de dados para outro servidor, determine se as entidades de banco de dados têm dependências de entidades de outro banco de dados. Se tiverem, você deve mover esses bancos de dados para o mesmo servidor.
Configurando failover para aplicativos que abrangem vários bancos de dados.
Você deseja assegurar a disponibilidade contínua do seu aplicativo, e a utilização de espelhamento de banco de dados como estratégia de failover. O aplicativo depende de mais de um banco de dados e você pretende assegurar a execução do aplicativo com êxito, mesmo que ele apresente failover no servidor espelho. Como o espelhamento trabalha no nível de banco de dados, é necessário determinar quais bancos de dados são críticos para o aplicativo, de forma que o espelhamento seja definido para todos, individualmente. Assegure, portanto, que todos os bancos de dados apresentem failover em conjunto, garantindo assim o funcionamento do aplicativo no servidor espelho.
Localizando entidades de aplicativo que executem consultas distribuídas com nomes de quatro partes.
Você deseja saber quais servidores vinculados estão sendo usados nas consultas distribuídas.
Localizando entidades usadas em um aplicativo que contenham referências dependentes de chamador ou referências a nome de uma parte.
Antes de implantar um aplicativo, determine se as entidades usadas pelo aplicativo contêm referências dependentes de chamador ou referências a entidades que usam somente nomes de uma parte. Essas referências indicam práticas de programação ineficazes, podendo resultar em comportamento inesperado durante a implantação do aplicativo. Isso se dá porque a resolução (associação) da entidade referenciada depende do esquema do chamador e essas informações não são determinadas até o tempo de execução. Depois de localizar as referências, as consultas podem ser corrigidas especificando o nome de várias partes apropriado, como schema_name.object_name.
Para obter mais informações sobre dependências de SQL, consulte Compreendendo dependências do SQL.
Relatando dependências pela utilização de exibições e funções do sistema
Para exibir dependências de SQL, o SQL Server 2008 fornece a exibição de catálogo sys.sql_expression_dependencies e as funções de gerenciamento dinâmico sys.dm_sql_referenced_entities e sys.dm_sql_referencing_entities. Você pode consultar esses objetos para retornar informações de dependência sobre entidades definidas pelo usuário.
As dependências de SQL também podem ser exibidas usando Dependências de Exibição em SQL Server Management Studio. Para obter mais informações, consulte Como exibir dependências SQL (SQL Server Management Studio).
Usando a exibição de catálogo sys.sql_expression_dependencies
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.
Usando a função de gerenciamento dinâmico sys.dm_sql_referenced_entities
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.
Usando a função de gerenciamento dinâmico sys.dm_sql_referencing_entities
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.
Exemplos
Os exemplos a seguir retornam dependências de SQL, usando a exibição de catálogo sys.sql_expression_dependencies e as funções de gerenciamento dinâmico sys.dm_sql_referenced_entities e sys.dm_sql_referencing_entities.
Relatando entidades das quais dependem uma entidade especificada
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
Relatando entidades que dependem de uma entidade especificada
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
Relatando dependências em nível de coluna
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
Relatando as dependências entre servidores e entre banco de dados
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
Relatando referências dependentes de chamador
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;
Relatando sobre entidades que usam um UDT especificado
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
Dependências de disparador DDL em nível de servidor de relatório
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;
Consulte também