Mapeando dependências entre tabelas

Boa Noite Pessoal,

Hoje estive fazendo uma exportação eventual entre bases de dados SQL Server. A exportação consistia em apenas 30 tabelas do banco de dados de produção para o banco de dados de homologação. Para uma tarefa tão simples, eu pude até me dar ao luxo de escolher como fazer isso. Import / Export Wizard, SSIS com um container ForEachLoop e scripts DML com Linked Servers foram algumas das minhas opções.

O problema não era escolher qual ferramenta já que todas eram boas opções. O problema é que nessa relação de 30 tabelas havia diversos relacionamentos e infelizmente não era possível exportar as tabelas em qualquer ordem já que exportar uma tabela em uma ordem incorreta leva fatalmente a uma violação de foreign keys.

A primeira coisa que pensei foi em excluir as chaves estrangeiras, exportar os dados e recriá-las, mas como o banco tinha 400 tabelas e não apenas 30 não era uma solução muito interessante já que se uma chave fosse excluída e não fosse recriada, brechas de integridade seriam abertas. Mapear todas as chaves envolvidas nas 30 tabelas era trabalhoso também.

Bom, provavelmente alguém já passou por isso, então para me ajudar e ajudar os que como eu esbarraram nesse problema, resolvi dar uma "fuçada" no catálogo do SQL Server 2005 e montar um script que me diga qual é a ordem correta para exportar as tabelas. Não foi preciso ir tão longe. Eu conhecia a sysconstraints do SQL Server 2000 e a Sys.Foreign_Keys do SQL Server 2005 e ao contrário do que eu pensava, apenas a Sys.Foreign_Keys já é suficiente. Estou honrando também uma promessa de um artigo anterior.

Para exemplificar como montar a ordem de exportação das tabelas, vou me utilizar do banco AdventureWorks que vem junto com o SQL Server 2005. Eu poderia utilizar o Northwind também ou quem sabe até o Pubs, mas prefiro utilizar o AdventureWorks que está disponível junto com o produto. Em todo caso, a solução funciona para qualquer banco de dados.

A view sys.foreign_keys tem a descrição de todas as chaves estrangeiras presentes no banco de dados. Algumas colunas que merecem atenção são:

  • Name: Nome da chave estrangeira
  • Object_ID: ID da chave estrangeira (é um apontamento para o ID em sys.objects e sys.all_objects)
  • Parent_Object_ID: ID da tabela que contém a chave estrangeira (é um apontamento para o ID em sys.objects e sys.all_objects)
  • Create_Date: Data de criação da chave estrangeira
  • Modify_Date: Data de alteração da chave estrangeira
  • Referenced_Object_ID: ID da tabela que tem a chave primária que é referenciada pela chave estrangeira (aqui está o pulo do gato)

Uma vez que os IDs são fornecidos, basta utilizarmos a função OBJECT_NAME para recuperar o nome do objeto. A consulta abaixo retorna a relação de todas as FKs e as tabelas das quais elas fazem parte.

SELECT
    Name AS
ChaveEstrangeira,
    OBJECT_NAME(Parent_Object_ID) AS Tabela
FROM SYS.FOREIGN_KEYS

Se evoluírmos um pouco mais, podemos ter o nome da chave estrangeira, a tabela que ela faz parte e a tabela que ela referencia, ou seja, a tabela que contém a chave primária. A consulta abaixo retorna esses dados:

SELECT
    Name AS
ChaveEstrangeira,
    OBJECT_NAME(Parent_Object_ID) AS TabelaFilho,
    OBJECT_NAME(Referenced_Object_ID) AS TabelaPai
FROM SYS.FOREIGN_KEYS

Se desejarmos saber todas as tabelas que possuem uma FK para a tabela Products, podemos adaptar a consulta acima. Se desejarmos saber todas as tabelas que Product referencia, também podemos fazer uma adaptação:

— Selecionar todas as tabelas que referenciam Product através de FKs
SELECT
    Name AS
ChaveEstrangeira,
    OBJECT_NAME(Parent_Object_ID) AS TabelaFilho,
    OBJECT_NAME(Referenced_Object_ID) AS TabelaPai
FROM SYS.FOREIGN_KEYS

WHERE Referenced_Object_ID = OBJECT_ID(‘Production.Product’)

— Selecionar todas as tabelas que Product referencia através de FKs
SELECT
   Name AS
ChaveEstrangeira,
   OBJECT_NAME(Parent_Object_ID) AS TabelaFilho,
   OBJECT_NAME(Referenced_Object_ID) AS TabelaPai
FROM SYS.FOREIGN_KEYS

WHERE Parent_Object_ID = OBJECT_ID(‘Production.Product’)

Podemos ver que na primeira consulta, tabelas como ProductCostHistory, ProductInventory e ProductListPriceHistory dependem de Product já que representam respectivamente o histórico do custo do produto, o inventário do produto e o histórico do preço do produto. Da mesma forma, a tabela Product depende das tabelas UnitMeasure, ProductModel, ProductSubcategory que representam a unidade de medida do produto, o modelo do produto e a subcategoria do produto.

Como podemos perceber, a tabela Product repassa sua PK para as tabelas ProductCostHistory, ProductInventory e ProductListPriceHistory e depende da PK das tabelas UnitMeasure, ProductModel, ProductSubcategory. Assim sendo, se desejarmos exportar essas tabelas, teríamos que exportar primeiro UnitMeasure, ProductModel, ProductSubcategory, posteriormente exportaríamos a tabela Product e por último as tabelas ProductCostHistory, ProductInventory e ProductListPriceHistory. O grande desafio é montar a lista.

Utilizando essas consultas, conseguimos mapear relacionamentos diretos entre as tabelas que dependem de Product e as tabelas de que Product depende, mas isso não nos dá a ordem de exportação. Precisamos lembrar também que existem outras tabelas envolvidas então não podemos simplesmente selecionar uma tabela e mapear suas dependências.

O detalhe para resolver esse problema é lembrar que existem relações de dependências indiretas. Se Product depende de UnitMeasure e ProductCostHistory depende de Product, então indiretamente ProductCostHistory depende de UnitMeasure e ProductCost e nesse caso UnitMeasure precisa ser migrada antes de ProductCost. Na verdade ela precisa estar entre as primeiras tabelas migradas, já que se não for migrada primeiro irá provocar problemas de violação de chave. Esse é mais um caso de relacionamentos hierárquicos e com uma CTE Recursiva, podemos resolver o problema.

Só que não basta utilizar uma CTE Recursiva, pois, existem situações particulares que podem inviabilizá-la. Um autorelacionamento por exemplo, iria tornar a CTE recursiva infinita e ele deve ser tratado antes. Outro ponto a ser trabalhado é que o SQL Server 2005 dá suporte a Schemas e é necessário exibir também os Schemas das tabelas para identificá-las de forma duplamente qualificada (Schema.Objeto). Primeiramente, tratemos o problema do nome com a Function abaixo:

— Cria uma função para mostrar o nome do objeto
CREATE FUNCTION dbo.RetornaNomeObjeto (@ID INT)
RETURNS SYSNAME
AS
BEGIN
    DECLARE
@Nome SYSNAME
    SELECT @Nome = SCHEMA_NAME(SCHEMA_ID) + ‘.’ + NAME
    FROM SYS.OBJECTS WHERE OBJECT_ID = @ID
    RETURN @Nome
END

Com essa função, basta passar o ID de um objeto que será retornado o nome do objeto acompanhado de seu Schema. Para evitar autorelacionamentos, basta colocar a condição WHERE Parent_Object_ID != Referenced_Object_ID. Essa condição impede que a tabela filho seja igual a tabela pai (condição para a existência de um auto relacionamento).

WITH Relacoes (Filho_ID, Pai_ID)
AS (
SELECT Parent_Object_ID, Referenced_Object_ID
FROM SYS.FOREIGN_KEYS
WHERE Parent_Object_ID != Referenced_Object_ID),

Dependencias (Filho_ID, TabelaFilho, Pai_ID, TabelaPai, Nivel)
AS (

SELECT Filho_ID, dbo.RetornaNomeObjeto(Filho_ID),
Pai_ID, dbo.RetornaNomeObjeto(Pai_ID), 1 AS Nivel
FROM Relacoes

UNION ALL

SELECT REL.Filho_ID, TabelaFilho, DEP.Pai_ID, TabelaPai, Nivel + 1
FROM Relacoes AS REL
INNER JOIN Dependencias AS DEP ON REL.Pai_ID = DEP.Filho_ID)

SELECT TabelaPai, COUNT(Nivel) AS Niveis FROM Dependencias
GROUP BY TabelaPai
ORDER BY Niveis DESC

A lógica dessa consulta é simples, as tabelas pai que tiverem a maior quantidade de níveis são as que tem a maior quantidade de tabelas dependentes. Nesse caso, essas tabelas devem ser as primeiras a serem migradas, para permitir que outras tabelas sejam migradas sem violações de chave. Se observarmos as três primeiras tabelas da lista (Production.UnitMeasure, Sales.SalesTerritory, Person.Contact) poderemos ver que elas não dependem de nenhuma outra tabela.

SELECT * FROM SYS.FOREIGN_KEYS
WHERE Parent_Object_ID IN (
    OBJECT_ID(‘Production.UnitMeasure’),
    OBJECT_ID(‘Sales.SalesTerritory’),
    OBJECT_ID(‘Person.Contact’))

A consulta resolve parte de nosso problema. Ela tem a relação de todas as tabelas que repassam suas PKs para outras tabelas como FK. Se migrarmos essas tabelas na ordem estipulada, não teremos problemas, mas embora a consulta tenha a ordem completa, ela não possui a relação de todas as tabelas. É preciso ainda colocar as tabelas restantes que não estão na lista. Finalizamos então nossa consulta com o operador UNION. As tabelas que não estejam na lista, recebem a quantidade de níveis igual a zero. Ex:

WITH Relacoes (Filho_ID, Pai_ID)
AS (
SELECT Parent_Object_ID, Referenced_Object_ID
FROM SYS.FOREIGN_KEYS
WHERE Parent_Object_ID != Referenced_Object_ID),

Dependencias (Filho_ID, TabelaFilho, Pai_ID, TabelaPai, Nivel)
AS (

SELECT Filho_ID, dbo.RetornaNomeObjeto(Filho_ID),
Pai_ID, dbo.RetornaNomeObjeto(Pai_ID), 1 AS Nivel
FROM Relacoes

UNION ALL

SELECT REL.Filho_ID, TabelaFilho, DEP.Pai_ID, TabelaPai, Nivel + 1
FROM Relacoes AS REL
INNER JOIN Dependencias AS DEP ON REL.Pai_ID = DEP.Filho_ID),

Lista (Tabela, Niveis)
AS (

SELECT TabelaPai, COUNT(Nivel) FROM Dependencias
GROUP BY TabelaPai

UNION ALL

SELECT
dbo.RetornaNomeObjeto(OBJECT_ID), 0 FROM sys.tables AS T
WHERE NOT EXISTS (
    SELECT Pai_ID FROM Dependencias
    WHERE Dependencias.Pai_ID = T.OBJECT_ID))

SELECT Tabela FROM Lista
ORDER BY Niveis DESC

Acho que com esse último script, o mapeamento da relação das tabelas fica mais fácil. Espero que os erros de FK diminuam a partir desse script. É possível também adaptá-lo para montar a relação de tabelas abaixo de uma tabela específica. Pode-se por exemplo, montar a lista de tabelas a serem migradas a partir da tabela de produtos. Nesse caso, o NOT EXISTS precisa de algumas adaptações. Deixo essa para os mais fuçadores.

[ ]s,

Gustavo

12 Respostas para “Mapeando dependências entre tabelas

  1. Glayson Junio

    Muito chic teu script amigo, vou utiliza-lo p/ atualização altomatica de banco de dados no meu sistema;Com ele posso verificar se existe o relacionamento ou n na base do meu cliente e criar o mesmo se for necessario;Glayson Junio(S&S Desenvolvimento de Sistemas);

  2. Olá Glayson,Eu fiz esse script inicialmente para algo desse tipo. Ele me atendeu bem na época, mas já estou vendo algumas lacunas para tratar em um artigo posterior. Ainda assim, bom saber que ele ajuda a outras pessoas.[ ]s,

  3. Caro Gustavo,parabéns, excelente artigo, me ajudou de forma imediata com uma situação hoje.[]´sPH.

  4. Oi Paulo,Obrigado. Ainda vou fazer a versão II qualquer hora dessas.Abs,

  5. Muito bom esse macete meu caro, resolveu o meu problema de migração

  6. Cara, vc teve a manha total!!!
    Parabens!!

  7. Iolanio de Faria Abreu

    Muito Bom seu script, me ajudou em uma migração de dados, para complementar coloquei apenas tabelas com registros acrescentando a condição abaixo no Select Lista.

    SELECT * FROM Lista
    where exists
    (
    select * from
    (Select object_name(id) Tabela,rowcnt as Linhas_Tabela from sysindexes
    where indid in (1,0) and objectproperty(id,’isusertable’)=1)x
    where x.Linhas_Tabela >0
    and ‘dbo.’+x.tabela=lista.tabela
    )
    ORDER BY Niveis DESC

    Valeu…Obrigado.

    • Olá Iolanio,

      Eu faria algumas correções no seu script. Pode ser interessante que você utilize a sys.partitions ao invés da sysindexes para obter as informações que você está precisando. Adicionalmente, ao invés de concatenar a string ‘dbo’, pode ser interessante você faz um join com a sys.schemas ou ainda pegar esse valor na coluna schema_id da sys.objects. Concatenações em cláusula WHERE envolvendo colunas não levam a planos eficientes.

      [ ]s,

      Gustavo

  8. olá, meu Nome é Itamar, parabéns pelo seu trabalho Gustavo, você sabe de algum script que resolva esse problema no PostgreSQL?

    • Olá Itamar,

      O PostgreeSQL também suporta o uso de CTEs, mas certamente as tabelas de catálogo são diferentes. Faz muito tempo que não esbarro com um postgree, mas acredito que a pg_depend pode ser um bom começo.

      [ ]s,

      Gustavo

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