Mapeando dependências de objetos para colunas

Boa Noite Pessoal,

Hoje vi no fórum de SQL Server da Microsoft uma necessidade relativamente comum que é a identificação de que objetos referenciam determinadas colunas de determinadas tabelas. É bem comum perguntarmos sobre essa dependências especialmente quando precisamos eliminar ou alterar uma ou outra coluna e sabemos que essas colunas são referenciadas por diversas views, procedures, functions, etc. O melhor para evitar tais situações é usar a opção SCHEMA BINDING sempre que possível, pois, ela evitará que uma ou outra coluna seja excluída e prejudique os objetos que delas dependam. É uma opção mais segura, mas ainda assim, para uma mudança é necessário mapear e isso o SCHEMA BINDING não faz.

Há muito tempo atrás, publiquei os artigos “Mapeando dependências entre tabelas” e “Mapeando dependências entre objetos” que trata sobre dependências com compatibilidade para o SQL Server 2005 (no 2008, os scripts funcionam, mas há outros objetos mais diretos). Ainda assim, não me atentei para uma necessidade de mapeamento para colunas. Vejamos então como podemos fazer isso:

CREATE TABLE Categorias (
    CodigoCategoria INT NOT NULL,
    NomeCategoria VARCHAR(15) NOT NULL,
    Descricao VARCHAR(MAX) NULL
    CONSTRAINT PK_Categorias PRIMARY KEY (CodigoCategoria))

CREATE TABLE Produtos (
    CodigoProduto INT NOT NULL,
    NomeProduto VARCHAR(40) NOT NULL,
    CodigoFornecedor INT NULL,
    CodigoCategoria INT NULL,
    QuantidadePorUnidade VARCHAR(25) NULL,
    PrecoUnitario MONEY NULL,
    UnidadesEstoque SMALLINT NULL,
    UnidadesPedidas SMALLINT NULL,
    NivelDeReposicao SMALLINT NULL,
    Descontinuado BIT NOT NULL,
    CONSTRAINT PK_Produtos PRIMARY KEY (CodigoProduto))

CREATE TABLE DetalhesPedido (
    NumeroPedido INT NOT NULL,
    CodigoProduto INT NOT NULL,
    PrecoUnitario MONEY NOT NULL,
    Quantidade SMALLINT NOT NULL,
    Desconto REAL NOT NULL,
    CONSTRAINT PK_DetalhesPedido PRIMARY KEY (NumeroPedido,CodigoProduto))

CREATE TABLE Pedidos (
    NumeroPedido INT NOT NULL,
    CodigoCliente INT NULL,
    CodigoFuncionario INT NULL,
    DataPedido DATE NULL,
    DataEntrega DATE NULL,
    DataEnvio DATE NULL,
    Frete MONEY NULL,
    CONSTRAINT PK_Pedidos PRIMARY KEY (NumeroPedido))

CREATE TABLE Clientes (
    CodigoCliente INT NOT NULL,
    NomeEmpresa VARCHAR(40) NOT NULL,
    NomeContato VARCHAR(30) NULL,
    CargoContato VARCHAR(30) NULL,
    Endereco VARCHAR(60) NULL,
    Cidade VARCHAR(15) NULL,
    Regiao VARCHAR(15) NULL,
    CEP CHAR(8) NULL,
    Pais VARCHAR(15) NULL,
    Telefone VARCHAR(24) NULL,
    Fax VARCHAR(24) NULL,
CONSTRAINT PK_Clientes PRIMARY KEY (CodigoCliente))

Agora que já criamos algumas tabelas, o próximo passo é criar algumas views, procedures, functions e triggers para criar o mapeamento dos objetos e das colunas desses objetos.

CREATE VIEW vProdutos As
SELECT NomeCategoria, NomeProduto
FROM Categorias As C
INNER JOIN Produtos As P ON C.CodigoCategoria = P.CodigoCategoria
GO

CREATE FUNCTION dbo.CalculaTotalPedido (@NumeroPedido INT)
RETURNS MONEY
As
BEGIN
DECLARE
@Ret MONEY
SET @Ret = (SELECT Frete FROM Pedidos WHERE NumeroPedido = @NumeroPedido)
SET @Ret = @Ret + (
    SELECT SUM(Quantidade * PrecoUnitario * (1 – Desconto))
    FROM DetalhesPedido WHERE NumeroPedido = @NumeroPedido)

RETURN (@Ret)
END
GO

CREATE PROCEDURE UspRetornaClientesPedidos
As
SELECT NomeEmpresa, NomeContato, Cidade, Pais, CEP
FROM Clientes As C
LEFT OUTER JOIN Pedidos As P ON C.CodigoCliente = P.CodigoCliente
GO

CREATE TRIGGER trgEstoque ON DetalhesPedido
FOR INSERT, UPDATE, DELETE
As
BEGIN

    — Efetua os incrementos
    UPDATE Produtos SET UnidadesEstoque += D.Quantidade
    FROM Produtos As P
    INNER JOIN DELETED As D ON P.CodigoProduto = D.CodigoProduto

    — Efetua os decrementos
    UPDATE Produtos SET UnidadesEstoque -= I.Quantidade
    FROM Produtos As P
    INNER JOIN INSERTED As I ON P.CodigoProduto = I.CodigoProduto

END

Visualmente podemos ver que a view, a function, a procedure e a trigger fazem referência para algumas colunas de algumas triggers. Utilizando a view de catálogo sys.sql_dependencies podemos mapear as dependências entre os objetos.

SELECT
    OBJECT_NAME(referenced_major_id) As ObjetoReferenciado,
    OBJECT_NAME(object_id) As Objeto
FROM sys.sql_dependencies
ORDER BY ObjetoReferenciado

O resultado de fato mostra as dependências:

Objeto Referenciado Objeto
Categorias vProdutos
Categorias vProdutos
Clientes UspRetornaClientesPedidos
Clientes UspRetornaClientesPedidos
Clientes UspRetornaClientesPedidos
Clientes UspRetornaClientesPedidos
Clientes UspRetornaClientesPedidos
Clientes UspRetornaClientesPedidos
DetalhesPedido CalculaTotalPedido
DetalhesPedido CalculaTotalPedido
DetalhesPedido CalculaTotalPedido
DetalhesPedido CalculaTotalPedido
Pedidos CalculaTotalPedido
Pedidos UspRetornaClientesPedidos
Pedidos CalculaTotalPedido
Produtos trgEstoque
Produtos vProdutos
Produtos vProdutos
Produtos trgEstoque

Podemos ver todos os mapeamentos, mas o fato de haver duplicidades é no mínimo intrigante. Usar o DISTINCT resolveria isso fácil, mas se há repetição há alguma razão para isso. Vamos pesquisar algumas outras colunas que provocam essa repetição:

SELECT
    OBJECT_NAME(referenced_major_id) As ObjetoReferenciado,
    OBJECT_NAME(object_id) As Objeto,
    column_id, referenced_minor_id

FROM sys.sql_dependencies
ORDER BY ObjetoReferenciado

Eis o resultado:

Objeto Referenciado Objeto Column_ID Referenced_minor_id
Categorias vProdutos 0 1
Categorias vProdutos 0 2
Clientes UspRetornaClientesPedidos 0 1
Clientes UspRetornaClientesPedidos 0 2
Clientes UspRetornaClientesPedidos 0 3
Clientes UspRetornaClientesPedidos 0 6
Clientes UspRetornaClientesPedidos 0 8
Clientes UspRetornaClientesPedidos 0 9
DetalhesPedido CalculaTotalPedido 0 1
DetalhesPedido CalculaTotalPedido 0 3
DetalhesPedido CalculaTotalPedido 0 4
DetalhesPedido CalculaTotalPedido 0 5
Pedidos CalculaTotalPedido 0 1
Pedidos UspRetornaClientesPedidos 0 2
Pedidos CalculaTotalPedido 0 8
Produtos trgEstoque 0 1
Produtos vProdutos 0 2
Produtos vProdutos 0 4
Produtos trgEstoque 0 7

O column_id não ajudou muito, mas a coluna Referenced_minor_id já é uma boa pista, pois, é visível que ela provoca a duplicidade. Se olharmos o Books OnLine, essa coluna é mais específica (Referenced_minor_id – 1, referenced_minor_id is a column ID; or if not a column, it is 0.). Só precisamos então fazer um JOIN com a sys.columns e um filtro para garantir que estamos sempre falando de colunas.

SELECT
    OBJECT_NAME(referenced_major_id) As ObjetoReferenciado,
    OBJECT_NAME(D.object_id) As Objeto,
    C.name As Coluna
FROM sys.sql_dependencies As D
INNER JOIN sys.columns As C ON
    D.referenced_major_id = C.object_id AND D.referenced_minor_id = C.column_id
WHERE class_desc = ‘OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND’
ORDER BY ObjetoReferenciado

Eis o resultado:

Objeto Referenciado Objeto Coluna
Categorias vProdutos CodigoCategoria
Categorias vProdutos NomeCategoria
Clientes UspRetornaClientesPedidos CodigoCliente
Clientes UspRetornaClientesPedidos NomeEmpresa
Clientes UspRetornaClientesPedidos NomeContato
Clientes UspRetornaClientesPedidos Cidade
Clientes UspRetornaClientesPedidos CEP
Clientes UspRetornaClientesPedidos Pais
DetalhesPedido CalculaTotalPedido NumeroPedido
DetalhesPedido CalculaTotalPedido PrecoUnitario
DetalhesPedido CalculaTotalPedido Quantidade
DetalhesPedido CalculaTotalPedido Desconto
Pedidos CalculaTotalPedido NumeroPedido
Pedidos UspRetornaClientesPedidos CodigoCliente
Pedidos CalculaTotalPedido Frete
Produtos trgEstoque CodigoProduto
Produtos vProdutos NomeProduto
Produtos vProdutos CodigoCategoria
Produtos trgEstoque UnidadesEstoque

Podemos ver que todas as colunas foram retornadas conforme o esperado. Não somente as colunas usadas em SELECTs e UPDATEs, mas também as colunas utilizadas em JOINs (e como isso faz diferença). Será que realmente funciona para outros casos ? Vejamos um objeto com a opção SCHEMA_BINDING (requerida em views indexadas inclusive).

CREATE VIEW vProdutos2
WITH SCHEMABINDING
As
SELECT
NomeCategoria, NomeProduto
FROM dbo.Categorias As C
INNER JOIN dbo.Produtos As P ON C.CodigoCategoria = P.CodigoCategoria
GO

Se tentarmos a consulta anterior, infelizmente a view vProdutos2 não será retornada. A razão é que o vínculo é com schema binding e aí precisamos alterar o predicado.

SELECT
    OBJECT_NAME(referenced_major_id) As ObjetoReferenciado,
    OBJECT_NAME(D.object_id) As Objeto,
    C.name As Coluna
FROM sys.sql_dependencies As D
INNER JOIN sys.columns As C ON

    D.referenced_major_id = C.object_id AND D.referenced_minor_id = C.column_id
WHERE class_desc IN (
    ‘OBJECT_OR_COLUMN_REFERENCE_NON_SCHEMA_BOUND’,
    ‘OBJECT_OR_COLUMN_REFERENCE_SCHEMA_BOUND’)
ORDER BY ObjetoReferenciado

Eis o resultado:

Objeto Referenciado Objeto Coluna
Categorias vProdutos CodigoCategoria
Categorias vProdutos NomeCategoria
Categorias vProdutos2 CodigoCategoria
Categorias vProdutos2 NomeCategoria
Clientes UspRetornaClientesPedidos CodigoCliente
Clientes UspRetornaClientesPedidos NomeEmpresa
Clientes UspRetornaClientesPedidos NomeContato
Clientes UspRetornaClientesPedidos Cidade
Clientes UspRetornaClientesPedidos CEP
Clientes UspRetornaClientesPedidos Pais
DetalhesPedido CalculaTotalPedido NumeroPedido
DetalhesPedido CalculaTotalPedido PrecoUnitario
DetalhesPedido CalculaTotalPedido Quantidade
DetalhesPedido CalculaTotalPedido Desconto
Pedidos CalculaTotalPedido NumeroPedido
Pedidos UspRetornaClientesPedidos CodigoCliente
Pedidos CalculaTotalPedido Frete
Produtos trgEstoque CodigoProduto
Produtos vProdutos NomeProduto
Produtos vProdutos CodigoCategoria
Produtos trgEstoque UnidadesEstoque
Produtos vProdutos2 CodigoProduto
Produtos vProdutos2 NomeProduto

Agora sim funcionou. Para mapeamentos simples, essa view é uma mão na roda. Fica aí a dica.

[ ]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