Localizando objetos que são referenciados em procedures do SQL Server

Levando em conta o uso do SQL Server como solução de banco de dados adotada em um projeto, é muito comum que aplicações que dependam deste produto empreguem stored procedures (também conhecidas como “procedimentos armazenados” ou, simplesmente, “procedures”). Este último tipo de objeto engloba instruções SQL voltadas à pesquisa de informações, operações de inclusão, atualização ou ainda, exclusão de registros.

Em termos gerais, stored procedures estendem as capacidades dos comandos normalmente utilizados para manipulação de tabelas relacionais, permitindo assim que elementos da programação convencional como estruturas de decisão e laços de repetição sejam empregados, algo praticamente impossível com os recursos-padrão da linguagem SQL.

Diversos são os motivos que levam à escolha por procedures na implementação de funcionalidades:

• Performance: procedures são geradas e pré-compiladas dentro de um repositório controlado pelo SGBD, fato este que possibilita uma maior velocidade na execução dos comandos definidos nas mesmas;
• Reuso: diversas aplicações podem utilizar uma mesma stored procedure, contribuindo assim para o reaproveitamento dos recursos oferecidos por tal objeto;
• Menor tráfego de rede: as stored procedures diminuem a transferência de informações através de uma rede por estarem concentradas num servidor, evitando assim que um volume grande de dados leve a uma degradação de performance no acesso a recursos remotos;
• Segurança: administradores de bancos de dados (DBAs) podem conceder apenas o direito de execução a stored procedures, evitando assim o acesso e a manipulação direta de tabelas com prováveis informações sensíveis;
• Ainda sobre a questão da segurança, o uso de procedures possibilita que uma aplicação fique menos sujeita a ataques de injeção de SQL (SQL Injection); este é um tipo de ação maliciosa bastante comum em soluções voltadas à Internet, em que instruções são inseridas em uma página HTML para conseguir o acesso indevido a informações ou, até mesmo, danificar estruturas presentes na base de dados.

Em sistemas complexos que possuam procedures é bastante comum que estas construções referenciem diversas tabelas, outros elementos deste tipo e, até mesmo, funções criadas na própria base de dados. Em algum momento (seja no transcorrer do projeto inicial de implementação ou durante a realização de alterações no mesmo) surgirá a necessidade de se determinar, por exemplo, em quais stored procedures um determinado campo e/ou tabela são referenciados.

Desde o SQL Server 2005 existe a view chamada sys.procedures. Trata-se de um objeto de sistema que possibilita o acesso a informações (definições conhecidas como "metadata") relativas a stored procedures. A partir desta view é possível não apenas obter dados como o nome das procedures presentes em uma base de dados (campo "name"), como também outras informações como as datas de criação (campo "create_date") e última alteração (campo "modify_date") destas estruturas. Existe ainda na view sys.procedures um identificador numérico único ("ID") para cada stored procedure, sendo que isto é representado pelo campo "object_id"; é justamente este item que servirá de base para a obtenção do texto que corresponde às instruções contidas em uma procedure.

Outros exemplos de views que disponibilizam informações a respeito de estruturas de uma base de dados são:

• sys.tables: retornada dados referentes a tabelas que constam no banco;
• sys.indexes: índices que foram criados para as diferentes tabelas existentes na base;
• sys.foreign_keys: chaves estrangeiras criadas para relacionar as diferentes tabelas;
• sys.triggers: triggers que correspondem a eventos envolvendo objetos como tabelas.

O texto em que constam os comandos que formam uma procedure pode ser recuperado através da função de sistema OBJECT_DEFINITION, a qual recebe como parâmetro o ID de um objeto que pertence ao banco de dados que está acessando. O resultado da invocação de OBJECT_DEFINITION a partir de um ID de uma procedure é, justamente, o texto que corresponde à implementação dessa estrutura. A partir disto, um comando LIKE pode ser aplicado para encontrar um texto que contenha aquilo que está procurando (tabela, campo, outra procedure, função etc.), com este procedimento sendo demonstrado na Listagem 1.

Listagem 1: Exemplo de comando que determina quais procedures referenciam o campo IdCategoria

SELECT name, create_date, modify_date
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%IdCategoria%'

A execução da instrução SQL especificada no exemplo devolverá então as procedures listadas na Figura 1. Tais registros foram retornados devido ao fato das stored procedures SP_CONSULTA_TOTAIS_CATEGORIAS (Listagem 2) e SP_CONSULTA_DADOS_PRODUTOS (Listagem 3) referenciarem um campo de nome IdCategoria em suas respectivas definições.

Figura 1: Resultado da Listagem 1

Listagem 2: Implementação da stored procedure SP_CONSULTA_TOTAIS_CATEGORIAS

CREATE PROCEDURE dbo.SP_CONSULTA_TOTAIS_CATEGORIAS
AS
BEGIN

    SELECT
        C.NomeCategoria,
        MIN(P.PrecoVenda) AS MenorPreco,
        MAX(P.PrecoVenda) AS MaiorPreco,
        AVG(P.PrecoVenda) AS PrecoMedio,
        SUM(P.QtdEstoque) AS QtdTotaisEstoque
    FROM Categoria C
    INNER JOIN Produto P
        ON P.CodigoCategoria = C.IdCategoria
    GROUP BY C.NomeCategoria

END
GO

Listagem 3: Implementação da stored procedure SP_CONSULTA_DADOS_PRODUTOS

CREATE PROCEDURE dbo.SP_CONSULTA_DADOS_PRODUTOS
(
    @NOME_PRODUTO VARCHAR(50) = NULL
)
AS
BEGIN

    SELECT
        P.IdProduto,
        C.NomeCategoria,
        P.NomeProduto,
        P.QtdEstoque,
        P.PrecoVenda
    FROM Produto P
    INNER JOIN Categoria C
        ON C.IdCategoria = P.CodigoCategoria
    WHERE @NOME_PRODUTO IS NULL OR
          UPPER(P.NomeProduto) LIKE '%' + UPPER(@NOME_PRODUTO) + '%'
    ORDER BY P.NomeProduto

END
GO

A técnica apresentada aqui pode contribuir não apenas para que desenvolvedores identifiquem stored procedures que façam uso de um objeto, como também prover informações que sirvam de base para que estes profissionais avaliem o impacto de prováveis mudanças que estarão realizando numa base.

Aqui chegamos ao fim deste artigo. Espero que o conteúdo exposto neste texto possa ser útil em atividades nas quais se utilizem procedures com o SQL Server. Obrigado e até uma próxima oportunidade!