The Ultimate Precompiled Procedure Searcher

SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

IF OBJECT_ID('Util.Util_ProcSearch') IS NOT NULL DROP PROCEDURE Util.Util_ProcSearch
GO

/*
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Util_ProcSearch
By Jesse Roberge - YeshuaAgapao@Yahoo.com

Searches precompiled procedures (stored procs,triggers,functions) for the provided search string.
LIKE wildcards are not automatic, which allows for easy 'begins with', 'ends with', and 'equal to' queries.
Also provides snippets of @CharCount length centered on the first found instance of the searched-for string

Update 2009-09-21:
Added @SchemaSearch to be able to filter by schema name patterns
Added views to the @Type default list (making the default 'all')
Change sort to sort by SchemaName before TableName

Update 2009-10-01:
Added searching of system procs/functions/views/triggers
Added parent object output (for triggers)

Required Input Parameters
@BodySearch VarChar(1000)The string to search proc/func/trigg bodies for.  Accepts LIKE Wildcards

Optional Input Parameters
@Type VarChar(100)='FN,IF,P,TF,TR,V'Type of objects to search.  Same codes as sys.objects.type
@NameSearch VarChar(250)=''Restrict body searches to procs that match this object name pattern.  Accepts LIKE Wildcards
@SchemaSearch VarChar(250)=''Restrict body searches to procs that match this schema name pattern.  Accepts LIKE Wildcards
@CharCount int=40Size of the snippets - # of chars before and after match
@UserOjbects TinyInt=1Search user objects
@SystemObjects TinyInt=0Search system objects

Usage
EXECUTE Util_ProcSearch '%order%', 'TR'
EXECUTE Util_ProcSearch @BodySearch='%cart%', @NameSearch='%order%', @SchemaSearch='dbo', @Type='P,TR', @CharCount=80

Copyright:
Licensed under the L-GPL - a weak copyleft license - you are permitted to use this as a component of a proprietary database and call this from proprietary software.
Copyleft lets you do anything you want except plagarize, conceal the source, or prohibit copying & re-distribution of this script/proc.

This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU Lesser General Public License as
    published by the Free Software Foundation, either version 3 of the
    License, or (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU Lesser General Public License for more details.

    see <http://www.fsf.org/licensing/licenses/lgpl.html> for the license text.

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
*/
CREATE PROCEDURE Util.Util_ProcSearch
@BodySearch VarChar(1000),
@Type VarChar(100)='FN,IF,P,TF,TR,V',
@NameSearch VarChar(250)='',
@SchemaSearch VarChar(250)='',
@CharCount int=40,
@UserObjects TinyInt=1,
@SystemObjects TinyInt=0
AS
--This CTE Removes Counter Table / fn_Numbers + fn_DelimitToTable prerequisites
WITH
N2   AS(SELECT 1 AS C UNION ALL SELECT 1),
N4   AS(SELECT 1 AS C FROM N2 AS A CROSS JOIN N2 AS B),
N16   AS(SELECT 1 AS C FROM N4 AS A CROSS JOIN N4 AS B),
N256   AS(SELECT 1 AS C FROM N16 AS A CROSS JOIN N16 AS B),
N65536   AS(SELECT 1 AS C FROM N256 AS A CROSS JOIN N256 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY C) AS Number FROM N65536)
SELECT
schemas.schema_id, objects.object_id,
schemas.name AS schema_name, objects.name AS object_name,
objects.type,
--Snippet output
substring(
objects.definition,
CASE
WHEN PatIndex(@BodySearch, objects.definition)-@CharCount<1 THEN 1
ELSE PatIndex(@BodySearch, objects.definition)-@CharCount
END,
CASE
WHEN
PatIndex(@BodySearch, objects.definition)+@CharCount+LEN(REPLACE(@BodySearch, '%', ''))-
CASE
WHEN PatIndex(@BodySearch, objects.definition)-@CharCount<1 THEN 1
ELSE PatIndex(@BodySearch, objects.definition)-@CharCount
END
> LEN(objects.definition)
THEN
LEN(objects.definition)-CASE
WHEN PatIndex(@BodySearch, objects.definition)-@CharCount<1 THEN 1
ELSE PatIndex(@BodySearch, objects.definition)-@CharCount
END
ELSE
PatIndex(@BodySearch, objects.definition)+@CharCount+LEN(REPLACE(@BodySearch, '%', ''))-
CASE
WHEN PatIndex(@BodySearch, objects.definition)-@CharCount<1 THEN 1
ELSE PatIndex(@BodySearch, objects.definition)-@CharCount
END
END
) AS Snippet,
parent_schemas.schema_id AS parent_schema_id, parent_objects.object_id AS parent_object_id,
parent_schemas.name AS parent_schema_name, parent_objects.name AS parent_object_name
FROM
--This derived table Removes Counter Table / fn_Numbers + fn_DelimitToTable prerequisites
(
SELECT SUBSTRING(@Type+',', PK_CountID, CHARINDEX(',', @Type+',', PK_CountID)-PK_CountID) AS Value
FROM (SELECT Number AS PK_CountID FROM Numbers WHERE Number <= LEN(@Type)) AS Counter
WHERE SubString(',' + @Type + ',', PK_CountID, 1)=','
) AS Types
JOIN (
SELECT
0 AS system_object, objects.object_id, objects.name, objects.schema_id,
type, objects.type_desc, objects.parent_object_id, sql_modules.definition
FROM
sys.objects
JOIN sys.sql_modules ON objects.object_id=sql_modules.object_id
WHERE
1=@UserObjects
AND objects.name LIKE CASE WHEN @NameSearch='' THEN objects.name ELSE @NameSearch END
AND sql_modules.definition LIKE CASE WHEN @BodySearch='' THEN '%' ELSE @BodySearch END
UNION ALL
SELECT
1 AS system_object, objects.object_id, objects.name, objects.schema_id,
objects.type, objects.type_desc, objects.parent_object_id, sql_modules.definition
FROM
sys.system_objects AS objects
JOIN sys.system_sql_modules AS sql_modules ON objects.object_id=sql_modules.object_id
WHERE
1=@SystemObjects
AND objects.name LIKE CASE WHEN @NameSearch='' THEN objects.name ELSE @NameSearch END
AND sql_modules.definition LIKE CASE WHEN @BodySearch='' THEN '%' ELSE @BodySearch END
) AS objects ON Types.value=objects.type COLLATE SQL_Latin1_General_CP1_CI_AS
JOIN sys.schemas ON objects.schema_id=schemas.schema_id
LEFT OUTER JOIN sys.all_objects AS parent_objects ON objects.parent_object_id=parent_objects.object_id
LEFT OUTER JOIN sys.schemas AS parent_schemas ON parent_objects.schema_id=parent_schemas.schema_id
WHERE schemas.name LIKE CASE WHEN @SchemaSearch='' THEN '%' ELSE @SchemaSearch END
ORDER BY
objects.type,
schemas.name, schemas.schema_id,
objects.name, objects.object_id
GO

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=