Compreendendo dependências do SQL
As dependências do SQL são referências por nome, utilizadas em expressões SQL, que tornam uma entidade dependente de outra entidade. Uma entidade que faz referência a outra entidade em sua definição, e essa definição é armazenada no catálogo do sistema, é denominada entidade de referência. Uma entidade que é referenciada por outra entidade é denominada entidade referenciada. Há dois tipos de dependências controladas pelo Mecanismo de Banco de Dados.
Dependência associada a esquema
A dependência associada a esquema é uma relação entre duas entidades que impede que a entidade referenciada seja cancelada ou modificada enquanto existir a entidade mencionada. A dependência associada a esquema é criada quando uma exibição ou função definida pelo usuário é criada usando a cláusula WITH SCHEMABINDING. Uma dependência associada a esquema também pode ser criada quando uma tabela faz referência a outra entidade, como uma função definida pelo usuário Transact-SQL, tipo definido pelo usuário ou coleção de esquema XML em uma restrição CHECK ou DEFAULT, ou na definição de uma coluna computada. A especificação de um objeto usando um nome de duas partes (schema_name.object_name) não se qualifica como referência associada a esquema.
Dependência não associada a esquema
Uma dependência não associada a esquema é uma relação entre duas entidades que não impede que a entidade referenciada seja cancelada ou modificada.
A ilustração a seguir mostra um exemplo de dependência SQL.
.gif "Representação de uma dependência SQL")
Na ilustração, há duas entidades: procedimento X e procedimento Y. O procedimento X contém uma expressão SQL que tem uma referência por nome no procedimento Y. O procedimento X é conhecido como entidade de referência, e o procedimento Y é conhecido como entidade referenciada. Como o procedimento X depende do procedimento Y, o procedimento X apresentará erro em tempo de execução se o procedimento Y não existir. Porém, o procedimento Y não falhará se o procedimento X não existir.
O exemplo a seguir mostra como o procedimento armazenado X pode depender do procedimento armazenado Y.
USE tempdb;
GO
CREATE PROCEDURE dbo.Y AS
SELECT * FROM sys.objects
GO
CREATE PROCEDURE dbo.X as
EXEC dbo.Y;
GO
Para exibir a dependência de X em Y, execute a consulta a seguir.
SELECT *
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('X')
AND referenced_id = OBJECT_ID('Y')
AND referenced_schema_name = 'dbo'
AND referenced_entity_name = 'Y'
AND referenced_database_name IS NULL
AND referenced_server_name IS NULL;
GO
Tipos de entidades de referência e referenciadas
A tabela a seguir lista os tipos de entidades para os quais as informações de dependência são criadas e mantidas. A tabela indica se a entidade é controlada como uma entidade de referência ou como entidade referenciada. As informações de dependência não são criadas nem mantidas para regras, padrões, tabelas temporárias, procedimentos armazenados temporários ou objetos do sistema.
Tipo de entidade |
Entidade de referência |
Entidade referenciada |
|---|---|---|
Tabela |
Sim* |
Sim |
Exibição |
Sim |
Sim |
Procedimento armazenado Transact-SQL** |
Sim |
Sim |
Procedimento armazenado CLR |
Não |
Sim |
Função Transact-SQL definida pelo usuário |
Sim |
Sim |
Função CLR definida pelo usuário |
Não |
Sim |
Gatilho CLR (DML e DDL) |
Não |
Não |
Gatilho DML Transact-SQL |
Sim |
Não |
Gatilho DDL no nível do banco de dados Transact-SQL |
Sim |
Não |
Gatilho DDL no nível do servidor Transact-SQL |
Sim |
Não |
Procedimentos armazenados estendidos |
Não |
Sim |
Fila |
Não |
Sim |
Sinônimo |
Não |
Sim |
Tipo (alias e tipo de dados CLR definido pelo usuário) |
Não |
Sim |
Coleção de esquemas XML |
Não |
Sim |
Função de partição |
Não |
Sim |
* Uma tabela é controlada como entidade de referência apenas quando se refere a um módulo Transact-SQL, tipo definido pelo usuário ou coleção de esquemas XML na definição de uma coluna computada, restrição CHECK ou restrição DEFAULT.
** Os procedimentos armazenados numerados com um valor inteiro maior que 1 não são controlados nem como entidade que faz referência nem como entidade referenciada.
Como as informações de dependência são controladas
O Mecanismo de Banco de Dados controla automaticamente informações de dependência quando entidades de referência são criadas, alteradas ou descartadas, e registra essas informações no catálogo do sistema do SQL Server. Por exemplo, se você criar um gatilho que faça referência a uma tabela, a dependência entre essas entidades será registrada. Se você descartar subsequentemente o gatilho, as informações de dependência serão removidas do catálogo de sistema.
Diferentemente das versões anteriores do SQL Server, em que as dependências eram controladas por ID, as dependências agora são controladas por nome. Isto significa que o Mecanismo de Banco de Dados localiza informações de dependência até mesmo entre duas entidades, caso a entidade referenciada não exista no momento em que a entidade de referência for criada. Essa circunstância pode ocorrer por causa da resolução de nome adiada. Por exemplo, um procedimento armazenado que faz referência a uma tabela pode ser criado com êxito, mas não executado, mesmo que a tabela referenciada não exista no banco de dados. O Mecanismo de Banco de Dados registra a dependência entre o procedimento e a tabela. No entanto, não é possível registrar uma ID para a tabela porque o objeto ainda não existe. Se a tabela for criada posteriormente, a ID da tabela será retornada com as outras informações de dependência.
As informações de dependência são controladas quando a entidade referenciada aparece por nome em uma expressão SQL persistente da entidade de referência. As informações de dependência são obtidas quando as entidades são referenciadas das seguintes maneiras:
Pelo uso de qualquer uma das instruções a seguir na definição de um Transact-SQLmódulo:
Instruções DML (Data Manipulation Language), como (SELECT, INSERT, UPDATE, DELETE, MERGE)
EXECUTE
DECLARE
SET (Quando SET é usada com função ou tipo definido pelo usuário. Por exemplo, DECLARE @var int; SET @var = dbo.udf1.)
Não são controladas as entidades referenciadas na definição de um módulo Transact-SQL pelo uso de instruções DDL (Data Definition Language), como CREATE, ALTER ou DROP.
Usando as instruções CREATE, ALTER ou DROP TABLE quando as instruções não estão em um módulo Transact-SQL e a entidade referenciada é uma função Transact-SQL definida pelo usuário, tipo definido pelo usuário ou coleção de esquema XML definidos em coluna computada, restrição CHECK ou restrição DEFAULT.
Dependências entre banco de dados e entre servidores
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 do servidor e do 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 nomes de várias partes, consulte convenções de sintaxe Transact-SQL (Transact-SQL).
As seguintes limitações se aplicam:
Não são controladas as dependências entre servidores para as instruções OPENROWSET, OPENQUERY e OPENDATASOURCE.
Não são controladas as dependências para a instrução EXEC ('…') AT linked_server.
A tabela a seguir resume as dependências entre servidores e entre bancos de dados que são controladas, e a informações registradas no catálogo do sistema e relatadas por sys.sql_expression_dependencies (Transact-SQL).
Expressão SQL em um módulo |
É controlada |
Nome de servidor referenciado |
Nome do banco de dados referenciado |
Nome do esquema referenciado |
Nome da entidade referenciada |
|---|---|---|---|---|---|
SELECT * FROM s1.db2.sales.t1 |
Sim |
s1 |
db2 |
sales |
t1 |
SELECT * FROM db3..t1 |
Sim |
|
db3 |
|
t1 |
EXEC db2.dbo.Proc1 |
Sim |
|
db2 |
dbo |
proc1 |
EXEC ('…') AT linked_srv1 |
Não |
|
|
|
|
EXEC linked_svr1.db2.sales.proc2 |
Sim |
linked_svr1 |
db2 |
sales |
proc2 |
Efeito de agrupamento em controle de dependência
Um agrupamento determina as regras que classificam e comparam dados. O agrupamento do banco de dados é usado para identificar informações de dependência por entidades dentro do banco de dados. Por exemplo, se um procedimento armazenado fizer referência às entidades Some_Table e SOME_TABLE em um banco de dados que usa um agrupamento com diferenciação de maiúsculas e minúsculas, as informações de dependência das duas entidades serão registradas porque a comparação dos dois nomes indica que eles não são os mesmos. Se o banco de dados usar um agrupamento sem diferenciação de maiúsculas e minúsculas, porém, apenas uma única dependência será registrada.
Com relação às dependências entre servidores e entre banco de dados, o agrupamento do servidor em que o objeto de referência reside é usado para resolver o nome do servidor e do banco de dados. O agrupamento do atual banco de dados é usado para resolver o nome do esquema e do objeto.
Considere a seguinte definição de procedimento armazenado. Se o procedimento armazenado for criado em um banco de dados com agrupamento que diferencia entre maiúsculas e minúsculas, em uma instância do SQL Server com agrupamento do servidor que diferencia maiúsculas e minúsculas, duas dependências serão registradas para as entidades srv_referenced.db_referenced.dbo.p_referenced e srv_referenced.db_referenced.DBO.P_REFERENCED.
CREATE PROCEDURE p_referencing AS
EXECUTE srv_referenced.db_referenced.dbo.p_referenced
EXECUTE srv_referenced.db_referenced.DBO.P_REFERENCED
EXECUTE SRV_REFERENCED.DB_REFERENCED.dbo.p_referenced;
Resolvendo referências ambíguas
Uma referência é ambígua quando pode resolver em tempo de execução para uma função definida pelo usuário, um UDT (Tipo Definido pelo Usuário) ou uma referência xquery para uma coluna do tipo xml.
Considere a seguinte definição de procedimento armazenado.
CREATE PROCEDURE dbo.p1 AS
SELECT column_a, Sales.GetOrder() FROM Sales.MySales;
No momento em que o procedimento armazenado é criado, não se sabe se Sales.GetOrder() é uma referência a uma função definida pelo usuário denominada GetOrder no esquema Sales, ou uma coluna denominada Sales do tipo UDT com um método denominado GetOrder(). Quando uma referência é ambígua, é relatada como ambígua através da definição das colunas is_ambiguous em sys.sql_expression_dependencies e sys.dm_sql_referenced_entities como 1. As informações de dependência a seguir são relatadas:
Dependência entre o procedimento armazenado e a tabela.
Dependência entre o procedimento armazenado e a função definida pelo usuário. Se a função existir, a ID da função será relatada; do contrário, a ID será NULL.
A dependência na função é marcada como ambígua. Ou seja, is_ambiguous é definida como 1.
Não são relatadas dependências em nível de coluna porque a instrução em que as colunas são referenciadas não pode ser associada.
Mantendo dependências
O Mecanismo de Banco de Dados mantém dependências associadas e não associadas por esquema. Essas dependências são atualizadas automaticamente durante qualquer operação que impacte o controle da dependência; por exemplo, ao atualizar um banco de dados de uma versão anterior do SQL Server ou ao alterar o agrupamento de um banco de dados.