Usando tabelas de sistema do MS SQL Server para encontrar procedures

Quando o seu banco de dados possui muitas tabelas e muitas stored procedures  controlar as dependências pode ser difícil e chato. Sempre que for alterar uma tabela terá que saber de antemão quais são as trocentas procedures que dependem dela.

A mesma coisa quando for alterar uma function, podem ter várias procedures que a usam.

Mesmo assim continuamos a trabalhar com procedures pela facilidade que elas nos dão de distribuir a carga do sistema, fazendo parte do processamento no servidor. Além disso a execução de procedures gera comandos de comprimento menor do que a execução de SQL Direto "ad hoc".

Para facilitar a busca de procedures que usam uma tabela ou que contenham uma determinada palavra-chave eu criei essa stored procedure abaixo, onde você simplesmente executa ela passando uma string entre sinais de % e ela te retorna todas as procedures que contenham essa palavra chave. Provavelmente essas você terá de alterar.

Para isso uso duas das tabelas de sistema do SQL Server. O exemplo é bem simples.

-- ################################################################################
-- # NOME   : SPVTR_Procura_Procedures
-- # PRODUTO  : Módulo: Manutenções de bancos de dados
-- # DEVELOPER  : Vitor Luiz Rubio
-- # COPYRIGHT  : 
-- # OBJETIVO  : Listar todos os nomes de procedures ou outros objetos que contenham em seu text determinada palavra ou tabela
-- # VERSAO   : 1.0
-- # DT. CRIACAO : 2008-01-02
-- # DT. ALTERACAO : 
-- #
-- # DEPENDENCIAS:
-- # Tabelas de sistema, sysobjects, syscomments
-- # DEPENDENTES:
-- #
-- # HISTÓRICO
-- #  2008-01-02 : Criação
-- #  
-- ################################################################################

  
CREATE PROCEDURE [dbo].[SPVTR_Procura_Procedures] (  
 @Procura varchar(255)
)  
AS  
  
SET NOCOUNT ON  
  
declare @SQL varchar(8000)

set @SQL = 'select distinct(O.Name) from sysobjects O (NOLOCK) inner join syscomments C (NOLOCK) on C.id = O.id 
where C.Text like' + '''' +  @Procura + ''''

exec (@SQL)

  
SET NOCOUNT OFF

Modo de usar:  SPVTR_Procura_Procedures '%Clientes%'


Espero que seja útil, have fun ^^