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()