sp_findit

use master

go

CREATE PROCEDURE [sp_findit] ( @value sysname )

AS

BEGIN
   SET NOCOUNT ON;
          SELECT distinct so.type

               , so.name

            FROM syscomments sc

      INNER JOIN sysobjects so

              ON sc.id=so.id

           WHERE sc.TEXT LIKE ‘%’ + @value + ‘%’

           UNION

          SELECT distinct so.type

               , so.name

            FROM syscolumns sc

      INNER JOIN sysobjects so

              ON sc.id = so.id

           WHERE sc.name LIKE ‘%’ + @value + ‘%’

        ORDER BY 1

               , 2
END –sp_findit()