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
Gustavo Maia Aguiar 