Technical Article

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]
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

Rate

3.5 (6)

Share

Share

Rate

3.5 (6)