Mapeando dependências entre objetos

Bom Dia Pessoal,

Uma das necessidades comuns na geração de scripts e alteração da estrutura de alguns objetos é o mapeamento de dependências entre objetos de banco de dados. Se desejarmos por exemplo replicar uma determinada procedure para outro banco de dados, será necessário mapear todas as tabelas que essa procedure utiliza. Ao alterar o tipo de dados de um determinada coluna é necessário saber todas as views que referenciam a tabela que possui aquela coluna.

O SQL Server 2005 possui uma View de metadados (sys.sql_dependencies) que mostra a relação entre objetos e podemos utilizá-la para mapear essas dependências e saber "quem" dependende de "quem". Vejamos um pequeno exemplo para entender como essa View pode nos ajudar nesse mapeamento. Utilizarei o TEMPDB para testes, pois, se esquecermos de excluir algum objeto, quando o SQL Server for reiniciado eles serão automaticamente excluídos.

— Muda o contexto para o TEMPDB
USE TEMPDB
GO

— Cria uma tabela T
CREATE TABLE T (COL INT)
GO

— Cria uma View V1 que acessa a tabela T
CREATE VIEW V1 AS SELECT COL * 2 AS DOBRO FROM T
GO

— Cria uma View V2 que acessa a tabela T
CREATE VIEW V2 AS SELECT COL * 3 AS TRIPLO FROM T, V1
GO

— Cria uma Procedure P1 que acessa a VIEW V1
CREATE PROCEDURE P1 AS SELECT DOBRO FROM V1
GO

— Cria uma Procedure P2 que executa a Procedure P1
CREATE PROCEDURE P2 AS EXEC P1
GO

— Mostra as dependências existentes entre os objetos
SELECT * FROM SYS.SQL_DEPENDENCIES
GO

— Mostra as colunas que "interessam"
SELECT OBJECT_NAME(OBJECT_ID) AS OBJETO,
OBJECT_NAME(REFERENCED_MAJOR_ID) AS DEPENDE_DE
FROM SYS.SQL_DEPENDENCIES

Através do último SELECT podemos ver o mapeamento das seguintes dependências:

– A tabela T não depende de ninguém
– A View V depende da tabela T
– A procedure P1 depende da View V
– A procedure P2 depende da procedure P1

Isso significa que caso seja necessário excluir a procedure P2, por exemplo, saberemos que a procedure P2 será impactada e precisamos nos prevenir para essa mudança. A View V2 pode ser excluída sem maiores problemas do ponto de vista de banco de dados, pois, embora ela dependa da tabela T e da VIEW V1, nenhum objeto depende dela. De forma resumida, se precisarmos saber que objetos dependem de alguém, basta filtrar a coluna REFERENCED_MAJOR_ID. Se precisarmos saber de que objetos alguém depende, basta filtrar a coluna Object_ID. Ex:

— Localizar todos os objetos dependentes da tabela T
SELECT OBJECT_NAME(OBJECT_ID) AS OBJETO,
OBJECT_NAME(REFERENCED_MAJOR_ID) AS DEPENDE_DE
FROM SYS.SQL_DEPENDENCIES
WHERE REFERENCED_MAJOR_ID = OBJECT_ID(‘T’)

— Localizar todos os objetos de que P1 depende
SELECT OBJECT_NAME(OBJECT_ID) AS OBJETO,
OBJECT_NAME(REFERENCED_MAJOR_ID) AS DEPENDE_DE
FROM SYS.SQL_DEPENDENCIES
WHERE OBJECT_ID = OBJECT_ID(‘P1’)

Essas consultas são interessantes para relações diretas, mas na maioria das vezes os mapeamentos não são tão simples assim. Se excluirmos por exemplo a tabela T, a consulta que utilizamos mostrará que apenas os objetos V1 e V2 serão afetados, mas sabemos que na prática se a tabela T for eliminada, todos os objetos utilizados
 serão eliminados. O que ocorre é que diretamente apenas V1 e V2 usam a tabela T, mas indiretamente, todos os objetos se baseiam nela. É necessário portanto, buscar uma solução que mostre tantos as referências diretas quanto as indiretas.

Se a coluna OBJECT_ID mostra o ID do objeto e a coluna REFERENCED_MAJOR_ID mostra o ID do objeto na qual OBJECT_ID depende, temos uma típica situação de hierarquias (bem na linha do Empregado e do Superior). Bastaria então utilizar o recurso de Recursive CTEs para recuperar as dependências indiretas. Ex:

WITH Relacoes (DEPENDENTEID, DEPENDENTENOME, OBJETOID)
AS (

— Recupera todas as relações de dependência
SELECT OBJECT_ID, OBJECT_NAME(OBJECT_ID),
REFERENCED_MAJOR_ID
FROM SYS.SQL_DEPENDENCIES),

Dependencias (DEPENDENTEID, DEPENDENTENOME, OBJETOID, OBJETONOME, Nivel)
AS (

SELECT DEPENDENTEID, DEPENDENTENOME, OBJETOID, OBJECT_NAME(OBJETOID), 1
FROM Relacoes

UNION ALL

SELECT REL.DEPENDENTEID, REL.DEPENDENTENOME, DEP.OBJETOID, OBJETONOME, Nivel + 1
FROM Relacoes AS REL
INNER JOIN Dependencias AS DEP ON REL.OBJETOID = DEP.DEPENDENTEID
)

— Selecionar todas as referências diretas e indiretas
SELECT DEPENDENTEID, DEPENDENTENOME, OBJETOID, OBJETONOME, Nivel
FROM Dependencias
ORDER BY DEPENDENTENOME

Através dessa consulta, podemos perceber que a procedure P1 depende diretamente da View V1 e indiretamente da tabela T já que se V1 dependente de T e P1 depende de T1, então P1 dependente indiretamente de T. Agora ficou fácil, verificar as dependências. Se você deseja saber todos os objetos que dependem de alguém, basta filtrar a CTE pela coluna OBJETONOME. Se desejar saber de que objetos alguém dependente, basta filtrar pela coluna DEPENDENTENOME. Ex:

— Listar todos os objetos que dependem de T
SELECT DEPENDENTEID, DEPENDENTENOME, OBJETOID, OBJETONOME, Nivel
FROM Dependencias
WHERE OBJETONOME = ‘T’
ORDER BY Nivel

— Listar todos os objetos de que P2 necessita
SELECT DEPENDENTEID, DEPENDENTENOME, OBJETOID, OBJETONOME, Nivel
FROM Dependencias
WHERE DEPENDENTENOME = ‘P2’
ORDER BY Nivel

O script é interessante, mas há algumas situações que não são cobertas pelo script. Se houver uma recursividade como por exemplo uma procedure que chama a si própria, o script irá gerar um erro. É uma situação pouco comum, mas se acontecer, é necessário fazer alguns ajustes (deixo essa por conta do mais curiosos).  Outra questão é que FKs não são mapeadas na tabela de dependências e nesse caso, o script não irá gerar as dependências necessárias entre as tabelas por conta das FKs (isso é um bom assunto para um outro artigo).

[ ]s,

Gustavo

2 Respostas para “Mapeando dependências entre objetos

  1. Pingback: Mapeando dependências entre objetos | Gustavo Maia Aguiar « Theodozio

  2. Pingback: Mapeando dependências de objetos para colunas | Gustavo Maia Aguiar

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s