Como pesquisar Stored Procedures pelo seu conteúdo

Olá pessoal, hoje vou passar aqui um código muito útil quando se trata de “Stored Procedures”, “Functions”, etc..

Esse código serve para pesquisar um trecho de texto dentro do conteúdo de todas as “Stored Procedures”, “Functions”, etc…, ou seja, em todos os objetos do banco de dados.

Isso é muito útil para evitar a duplicação de código, pois é comum criar uma “Stored Procedure” ou mesmo uma “Function” com o mesmo objetivo de outra já existente, simplesmente pelo fato de não sabermos da existência desta.

Por exemplo, vamos dizer que eu queira criar uma “Stored Procedure” que crie um usuário, e nessa “SP (Stored Procedure)” eu vou gravar os dados do usuário na tabela “userData”, então, antes de criar a “SP”, vou pesquisar por outras que já insiram registros nessa tabela. Dessa forma, o texto pelo qual vou procurar na “SP” é “insert into userData”, como segue abaixo:

select distinct o.name as Object_Name,
                o.type_desc
from sys.sql_modules m
inner join sys.objects o on (m.object_id = o.object_id)
where m.definition like '%insert into userData%';

O select irá retornar o objeto e o seu tipo: “Stored Procedure”, “Function”, etc…, dessa forma saberemos que já existe um objeto que insere dados na “userData”, dessa forma podemos abrir a “SP” ou “Function” para analisar se sua funcionalidade nos atende de modo que podemos evitar a criação de procedimentos duplicados.

Aperfeiçoando um pouco mais o código, podemos pesquisar por diversos trechos de texto ao mesmo tempo com um simples “AND”, como por exemplo:

select distinct o.name as Object_Name,
                o.type_desc
from sys.sql_modules m
inner join sys.objects o on (m.object_id = o.object_id)
where m.definition like '%insert into userData%' and
      m.definition like '%BirthDate%';

 Que retornará todos objetos que possuam no seu procedimento estes dois trechos de texto.

Podemos refinar a consulta usando os curingas “%” para encontrar objetos que possuam determinado texto no inicio ou no final da linha, alterando a string do “LIKE” de “’%insert into userData%’” para:

'insert into userData%' //Linhas que iniciem com este texto
'%insert into userData' //Linhas que terminem com essa string.

É isso ai pessoal, dúvidas, comentem abaixo.

Abraço.