SET nocount ON

DECLARE @string VARCHAR(1000)

SET @string = '' --> This is your search criteria

DECLARE
  @errnum INT,
  @errors CHAR(1),
  @rowcnt INT,
  @output VARCHAR(255)

SELECT
  @errnum = 0,
  @errors = 'N',
  @rowcnt = 0,
  @output = ''        

DECLARE @Results TABLE (
   Name VARCHAR(55),
   Type VARCHAR(12),
   DateCreated DATETIME,
   ProcLine VARCHAR(4000)
  )


INSERT
INTO @Results
  SELECT DISTINCT
    'Name' = CONVERT(VARCHAR(55), SO.name),
    'Type' = SO.type,
    crdate,
    ''
  FROM
    sysobjects SO
    JOIN syscomments SC
      ON SC.id = SO.id
  WHERE
    SC.text LIKE '%' + @string + '%'
  UNION
  SELECT DISTINCT
    'Name' = CONVERT(VARCHAR(55), SO.name),
    'Type' = SO.type,
    crdate,
    ''
  FROM
    sysobjects SO
  WHERE
    SO.name LIKE '%' + @string + '%'
  UNION
  SELECT DISTINCT
    'Name' = CONVERT(VARCHAR(55), SO.name),
    'Type' = SO.type,
    crdate,
    ''
  FROM
    sysobjects SO
    JOIN syscolumns SC
      ON SC.id = SO.ID
  WHERE
    SC.name LIKE '%' + @string + '%'
  ORDER BY
    2,
    1

SELECT
  Name,
  'Type' = CASE (Type)
             WHEN 'P' THEN 'Procedure'
             WHEN 'TR' THEN 'Trigger'
             WHEN 'X' THEN 'Xtended Proc'
             WHEN 'U' THEN 'Table'
             WHEN 'C' THEN 'Check Constraint'
             WHEN 'D' THEN 'Default'
             WHEN 'F' THEN 'Foreign Key'
             WHEN 'K' THEN 'Primary Key'
             WHEN 'V' THEN 'View'
             ELSE Type
           END,
  DateCreated
FROM
  @Results
ORDER BY
  2,
  1