sp_Find2

,

Install it in the master database and call it from any database on the system. 

Based on a script originally created by Michael F. Berry and later modified by Bill Lescher.

You can search for up to 4 keywords.  Allows you to search through SPs, UDFs, Triggers, SSIS packages, jobs, table names, and column names. 

Look to the top of the SP to find examples of how to run the SP.

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[sp_Find2]    Script Date: 04/18/2012 13:38:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE Procedure [dbo].[sp_Find2] 
	  @SearchText1 varchar(100) = ''
	, @SearchText2 varchar(100) = ''
	, @SearchText3 varchar(100) = ''
	, @SearchText4 varchar(100) = ''
	, @DBName sysname = Null
--	, @PreviewTextSize int = 200 
	, @SearchDBsFlag char(1) = 'Y'
	, @SearchJobsFlag char(1) = 'Y'
	, @SearchSSISFlag char(1) = 'Y'
As
/*
* Created: 12/19/06, Michael F. Berry (SQL Server Magazine contributor)
*
* Modified: 01/25/07, Michael F. Berry, Make it output to one main recordset for clarity
* Modified: 09/04/08, Bill Lescher and Chase Jones, Updated for SQL2005 and added Jobs & SSIS Packages
* Modified: 07/22/09, Bill L, Returning the PreviewText
* Modified: 04/17/12, Said Khorramshahgol, Enabled searching for Tables & Columns
* Modified: 04/17/12, Said Khorramshahgol, Enabled searching for multiple keywords
* Modified: 04/17/12, Said Khorramshahgol, PreviewText column now shows column size
* Modified: 04/17/12, Said Khorramshahgol, Enabled search in Master and MSDB databases
* Modified: 04/17/12, Said Khorramshahgol, PreviewText column size is now set at 200 characters
*
* Description: Find any string within the T-SQL code on this SQL Server instance, specifically
*				Database objects and/or SQL Agent Jobs and/or SSIS Packages
*
* Test: sp_Find4 'KEYWORD 1'	-- Search for 1 keyword across all objects/DBs/SSIS/Jobs
*		sp_Find4 'KEYWORD 1', 'KEYWORD2'	-- Search for 2 keywords across all objects/DBs/SSIS/Jobs
*		sp_Find4 'track', NULL, NULL, NULL, 'Common' 
*		sp_Find4 'track', NULL, NULL, NULL, 'Common', 'Y', 'N', 'N' --DB Only
*		sp_Find4 'track', NULL, NULL, NULL, 'Common', 'N', 'N', 'Y' --SSIS Only
*/
Set Transaction Isolation Level Read Uncommitted;
Set Nocount On;

DECLARE @PreviewTextSize int 
SET @PreviewTextSize = 200 

Create Table #FoundObject (
	  DatabaseName sysname
	, ObjectName sysname
	, ObjectTypeDesc nvarchar(60)
	, PreviewText varchar(max))--To show a little bit of the code and other info

Declare	@SQL as nvarchar(max);

/**************************
*  Database Search
***************************/
If @SearchDBsFlag = 'Y'
Begin
	If @DBName Is Null --Loop through all normal user databases
	Begin
		Declare ObjCursor Cursor Local Fast_Forward For 
			Select	[Name]
			From	Master.sys.Databases
			Where	[Name] Not In ('AdventureWorks', 'AdventureWorksDW', 'Distribution', 'Model', 'TempDB') 
			-- ('AdventureWorks', 'AdventureWorksDW', 'Distribution', 'Master', 'MSDB', 'Model', 'TempDB');

		Open ObjCursor;

		Fetch Next From ObjCursor Into @DBName;
		While @@Fetch_Status = 0
		Begin
			Select @SQL = '
				Use [' + @DBName + ']

				Insert Into #FoundObject (
					  DatabaseName
					, ObjectName
					, ObjectTypeDesc
					, PreviewText)
				Select	Distinct
						  ''' + @DBName + '''
						, sch.[Name] + ''.'' + obj.[Name] as ObjectName
						, obj.Type_Desc
						, Replace(Replace(SubString(mod.Definition, CharIndex(''' + @SearchText1 + ''', mod.Definition) - ' + Cast(@PreviewTextSize / 2 As varchar) + ', ' + 
							Cast(@PreviewTextSize As varchar) + '), char(13) + char(10), ''''), ''' + @SearchText1 + ''', ''***' + @SearchText1 + '***'')
				From 	sys.objects obj 
				Inner Join sys.SQL_Modules mod On obj.Object_Id = mod.Object_Id
				Inner Join sys.Schemas sch On obj.Schema_Id = sch.Schema_Id
				Where	mod.Definition Like ''%' + @SearchText1 + '%''
					AND  mod.Definition Like ''%' + COALESCE(@SearchText2, '') + '%''
					AND  mod.Definition Like ''%' + COALESCE(@SearchText3, '') + '%''
					AND  mod.Definition Like ''%' + COALESCE(@SearchText4, '') + '%''';
--				Order By ObjectName';

			Exec dbo.sp_executesql @SQL;
			
			Select @SQL = '
				Use [' + @DBName + ']

			Insert Into #FoundObject (
					  DatabaseName
					, ObjectName
					, ObjectTypeDesc
					, PreviewText)
				Select	TABLE_CATALOG, TABLE_SCHEMA+''.''+TABLE_NAME, TABLE_TYPE, ''Table definitions are not set up yet''
				From 	information_schema.tables
				Where	TABLE_NAME like ''%' + @SearchText1 + '%''
					AND TABLE_NAME LIKE ''%' + COALESCE(@SearchText2, '') + '%''
					AND TABLE_NAME LIKE ''%' + COALESCE(@SearchText3, '') + '%''
					AND TABLE_NAME LIKE ''%' + COALESCE(@SearchText4, '') + '%'''; 
								
			Exec dbo.sp_executesql @SQL;


			Select @SQL = '
				Use [' + @DBName + ']

			Insert Into #FoundObject (
					  DatabaseName
					, ObjectName
					, ObjectTypeDesc
					, PreviewText)
				Select	TABLE_CATALOG, TABLE_SCHEMA+''.''+TABLE_NAME+''.''+COLUMN_NAME, ''COLUMN'', DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(100)) + '')''
				From 	information_schema.columns
				Where	COLUMN_NAME like ''%' + @SearchText1 + '%''
					AND COLUMN_NAME LIKE ''%' + COALESCE(@SearchText2, '') + '%''
					AND COLUMN_NAME LIKE ''%' + COALESCE(@SearchText3, '') + '%''
					AND COLUMN_NAME LIKE ''%' + COALESCE(@SearchText4, '') + '%'''; 
				
			Exec dbo.sp_executesql @SQL;


		Fetch Next From ObjCursor Into @DBName;
		End;

		Close ObjCursor;

		Deallocate ObjCursor;
	End
	Else --Only look through given database
	Begin
			Select @SQL = '
				Use [' + @DBName + ']

				Insert Into #FoundObject (
					  DatabaseName
					, ObjectName
					, ObjectTypeDesc
					, PreviewText)
				Select	Distinct
						  ''' + @DBName + '''
						, sch.[Name] + ''.'' + obj.[Name] as ObjectName
						, obj.Type_Desc
						, Replace(Replace(SubString(mod.Definition, CharIndex(''' + @SearchText1 + ''', mod.Definition) - ' + Cast(@PreviewTextSize / 2 As varchar) + ', ' + 
							Cast(@PreviewTextSize As varchar) + '), char(13) + char(10), ''''), ''' + @SearchText1 + ''', ''***' + @SearchText1 + '***'')
				From 	sys.objects obj 
				Inner Join sys.SQL_Modules mod On obj.Object_Id = mod.Object_Id
				Inner Join sys.Schemas sch On obj.Schema_Id = sch.Schema_Id
				Where	mod.Definition Like ''%' + @SearchText1 + '%'' 
					AND  mod.Definition Like ''%' + COALESCE(@SearchText2, '') + '%''
					AND  mod.Definition Like ''%' + COALESCE(@SearchText3, '') + '%''
					AND  mod.Definition Like ''%' + COALESCE(@SearchText4, '') + '%''';
					
			Exec dbo.sp_ExecuteSQL @SQL;
	End;

	Select 'Database Objects' As SearchType;

	Select
		  DatabaseName
		, ObjectName
		, ObjectTypeDesc As ObjectType
		, PreviewText
	From	#FoundObject
	Order By DatabaseName, ObjectName;
End

/**************************
*  Job Search
***************************/
If @SearchJobsFlag = 'Y'
Begin
	Select 'Job Steps' As SearchType;


	Select	  j.[Name] As [Job Name]
			, s.Step_Id As [Step #]
			, Replace(Replace(SubString(s.Command, CharIndex(@SearchText1, s.Command) - @PreviewTextSize / 2, @PreviewTextSize), char(13) + char(10), ''), @SearchText1, '***' + @SearchText1 + '***') As Command
	From	MSDB.dbo.sysJobs j
	Inner Join MSDB.dbo.sysJobSteps s On j.Job_Id = s.Job_Id 
	Where	s.Command Like '%' + @SearchText1 + '%'
		AND s.Command Like '%' + COALESCE(@SearchText2, '') + '%'
		AND s.Command Like '%' + COALESCE(@SearchText3, '') + '%'
		AND s.Command Like '%' + COALESCE(@SearchText4, '') + '%';

End

/**************************
*  SSIS Search
***************************/
If @SearchSSISFlag = 'Y'
Begin
	Select 'SSIS Packages' As SearchType;

	Select	  [Name] As [SSIS Name]
			, Replace(Replace(SubString(Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)), CharIndex(@SearchText1, Cast(Cast(PackageData As varbinary(Max)) As varchar(Max))) -
				@PreviewTextSize / 2, @PreviewTextSize), char(13) + char(10), ''), @SearchText1, '***' + @SearchText1 + '***') As [SSIS XML]
	From	MSDB.dbo.sysDTSPackages90
	Where	Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)) Like '%' + @SearchText1 + '%'
		AND Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)) Like '%' + COALESCE(@SearchText2, '') + '%'
		AND Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)) Like '%' + COALESCE(@SearchText3, '') + '%'
		AND Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)) Like '%' + COALESCE(@SearchText4, '') + '%';
End


GO

Rate

3.5 (6)

Share

Share

Rate

3.5 (6)