sp_Find2

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