Search string or object used in all databases

USE [master]

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

/*

* 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')

and state_desc = 'ONLINE'

-- ('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