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
