The Ultimate Precompiled Procedure Searcher

,

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
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=40 Size of the snippets - # of chars before and after match
@UserOjbects TinyInt=1 Search user objects
@SystemObjects TinyInt=0 Search 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.

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

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=40							Size of the snippets - # of chars before and after match
	@UserOjbects TinyInt=1						Search user objects
	@SystemObjects TinyInt=0					Search 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

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

Rate

4.33 (6)

Share

Share

Rate

4.33 (6)