Personal WebSite of Leonardo Sasso

/***************************************************************

 ** Busca un texto dentro de todas las SP de la base de datos **
 ** www.leonardosasso.com.ar                                  **

 ***************************************************************/

Declare @vTexto VarChar (8000)

Set @vTexto = '<poner el texto a buscar>'

Select Objeto     = Convert (VarChar (50), SO.Name),

       TipoObjeto = AO.Type_Desc,

       Entorno    = Replace (SubString (SC.Text, CharIndex(@vTexto, SC.Text) - 30, 60 + Len (@vTexto)), Char(13) + Char(10), ' <Enter> ')

  From Sys.SysObjects SO Inner Join Sys.All_Objects AO On AO.Object_Id = SO.Id

                         Inner Join Sys.SysComments SC ON SC.Id        = SO.Id

 Where SC.Text  Like '%' + @vTexto + '%'

   And AO.Type_Desc In ('SQL_STORED_PROCEDURE', 'VIEW')