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!

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

Veja neste artigo como empregar instruções SELECT para determinar se elementos como campos, tabelas, procedures e outras construções são utilizadas dentro de uma stored procedure no SQL Server.

Artigos relacionados