SQL Search Function

Here is a SQL Search procedure that will search syscomments for any text in stored procedures, triggers, funtions, views, etc. for a single database.  It supports ! for NOT LIKE and | for OR as the first character of a search parameter.  It uses the flattening technique on syscoments because SQL Server will split a long stored procedure into multiple rows, and this allows a search across the row boundaries.  I also went with dynamic SQL because it gave me better speed and flexibility.  I wasn't able to come up with a good grouping mechanism using parens when mixing ANDs and ORs, so just remember AND has a higher precedence than OR.  It currently excludes defaults, so take out the AND so.xtype IN line to include these.  If you have very long sprocs, uncomment out the SET @maxrow line.

CREATE Procedure [dbo].[uspSearch]

  @s1 Varchar(255), -- Search parameter

  @s2 Varchar(255) = NULL,

  @s3 Varchar(255) = NULL,

  @s4 Varchar(255) = NULL,

  @s5 Varchar(255) = NULL,

  @show_text BIT = 0 -- Outputs the text of the procedure

AS

SET NOCOUNT ON

SET CONCAT_NULL_YIELDS_NULL OFF

-- -----------------------------------------------------------------------------------

--  Purpose: Search all stored procedure for given text parameters.  Uses LIKE search.

--    Notes: Use ! for NOT LIKE or | for OR as the first character of any search param

-- Creation: DW 04/11

-- -----------------------------------------------------------------------------------

DECLARE @sql NVarchar(Max), @i INT = 1, @s Varchar(255), @maxrow INT = 15

IF @s1 IS NULL RETURN

--SET @maxrow = (SELECT MAX(colid) FROM syscomments)

SET @sql = '

SET CONCAT_NULL_YIELDS_NULL OFF;

SELECT [name], xtype '

IF @show_text = 1 SET @sql = @sql + ', Char(10) + stext AS stext '

SET @sql = @sql + '

  FROM (

SELECT Max(so.name) AS [name], MAX(so.xtype) AS xtype, stext = '

WHILE @i <= @maxrow

BEGIN

  SET @sql = @sql + 'CONVERT(VARCHAR(MAX), MAX(CASE WHEN sc.colid = ' +

    CONVERT(VARCHAR, @i) + ' THEN sc.text END)) + '

  SET @i = @i + 1

END

SET @sql = @sql + ' NULL

  FROM syscomments sc JOIN sysobjects so ON sc.id = so.id

 WHERE sc.text IS NOT NULL

   AND so.xtype IN (''P'',''TR'',''FN'',''TF'',''C'',''V'')

 GROUP BY sc.id

) t WHERE 1 = 1 '

SET @i = 1

WHILE @i <= 5

BEGIN

  SET @s = CASE @i WHEN 1 THEN @s1 WHEN 2 THEN @s2 WHEN 3 THEN @s3

           WHEN 4 THEN @s4 WHEN 5 THEN @s5 END

  IF @s IS NOT NULL

  BEGIN

    IF LEFT(@s, 1) = '!'

      SET @sql = @sql + '

   AND stext NOT LIKE ' + QUOTENAME('%' + SUBSTRING(@s, 2, 254) + '%', '''')

    ELSE IF LEFT(@s, 1) = '|'

      SET @sql = @sql + '

    OR stext LIKE ' + QUOTENAME('%' + SUBSTRING(@s, 2, 254) + '%', '''')

    ELSE

      SET @sql = @sql + '

   AND stext LIKE ' + QUOTENAME('%' + @s + '%', '''')

  END

  SET @i = @i + 1

END

SET @sql = @sql + '

 ORDER BY xtype, [name]'

--PRINT @sql

EXECUTE sp_executesql @sql