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]
FromMaster.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)
SelectDistinct
  ''' + @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
Wheremod.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)
SelectTABLE_CATALOG, TABLE_SCHEMA+''.''+TABLE_NAME, TABLE_TYPE, ''Table definitions are not set up yet''
From information_schema.tables
WhereTABLE_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)
SelectTABLE_CATALOG, TABLE_SCHEMA+''.''+TABLE_NAME+''.''+COLUMN_NAME, ''COLUMN'', DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(100)) + '')''
From information_schema.columns
WhereCOLUMN_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)
SelectDistinct
  ''' + @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
Wheremod.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
FromMSDB.dbo.sysJobs j
Inner Join MSDB.dbo.sysJobSteps s On j.Job_Id = s.Job_Id 
Wheres.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]
FromMSDB.dbo.sysDTSPackages90
WhereCast(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