Rechercher une chaîne de caractères dans une base SQLSERVER

Il peut être utile de rechercher une chaîne de caractères dans tous les composants d'une base SQLSERVER.

Nous allons dans un premier temps déclarer une procédure stockée <sp_Find> qui sera en charge de cette recherche.

Voici le script.

USE [ma_base]
Go

If Exists ( Select 1 
From sys.objects 
Where object_id = object_id(N'dbo.sp_Find') 
And Type = 'P')
Drop Procedure dbo.sp_Find;
Go

Create Procedure [dbo].[sp_Find] 
 @SearchText varchar(8000)
, @DBName sysname = Null
, @PreviewTextSize int = 100
, @SearchDBsFlag char(1) = 'Y'
, @SearchJobsFlag char(1) = 'Y'
, @SearchSSISFlag char(1) = 'Y'
As

Set Transaction Isolation Level Read Uncommitted;
Set Nocount On;

Create Table #FoundObject (
 DatabaseName sysname
, ObjectName sysname
, ObjectTypeDesc nvarchar(60)
, PreviewText varchar(max))

Declare @SQL as nvarchar(max);

Select 'Chercher pour : ''' + @SearchText + '''' As CurrentSearch;

/* rechercher dans les bases utilisateur */

If @SearchDBsFlag = 'Y'

Begin

If @DBName Is Null 

Begin

Declare ObjCursor Cursor Local Fast_Forward For 

Select [Name]

From Master.sys.Databases

Where [Name] Not In ('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(''' + @SearchText + ''', mod.Definition) - ' + Cast(@PreviewTextSize / 2 As varchar) + ', ' + 

Cast(@PreviewTextSize As varchar) + '), char(13) + char(10), ''''), ''' + @SearchText + ''', ''***' + @SearchText + '***'')

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 ''%' + @SearchText + '%'' 

Order By ObjectName';

Exec dbo.sp_executesql @SQL;

Fetch Next From ObjCursor Into @DBName;

End;

Close ObjCursor;

Deallocate ObjCursor;

End

Else

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(''' + @SearchText + ''', mod.Definition) - ' + Cast(@PreviewTextSize / 2 As varchar) + ', ' + 

Cast(@PreviewTextSize As varchar) + '), char(13) + char(10), ''''), ''' + @SearchText + ''', ''***' + @SearchText + '***'')

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 ''%' + @SearchText + '%'' 

Order By ObjectName';

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

/*  Rechercher dans les Jobs */

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(@SearchText, s.Command) - @PreviewTextSize / 2, @PreviewTextSize), char(13) + char(10), ''), @SearchText, '***' + @SearchText + '***') As Command

From MSDB.dbo.sysJobs j

Inner Join MSDB.dbo.sysJobSteps s On j.Job_Id = s.Job_Id 

Where s.Command Like '%' + @SearchText + '%';

End

/*  Rechercher dans SSIS (packages) */

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(@SearchText, Cast(Cast(PackageData As varbinary(Max)) As varchar(Max))) -
@PreviewTextSize / 2, @PreviewTextSize), char(13) + char(10), ''), @SearchText, '***' + @SearchText + '***') As [SSIS XML]
From MSDB.dbo.sysDTSPackages90
Where Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)) Like '%' + @SearchText + '%';
End

Go

Comment utiliser cette procédure ?

EXEC dbo.sp_Find 'votre_chaine_a_rechercher', DBName, PreviewTextSize, SearchDBsFlag, SearchJobsFlag, SearchSSISFlag

avec

<DBName> nom d'une base spécifique pour la recherche - par défaut toutes les bases

<PreviewTextSize> nombre de caractères entourant la chaîne de caractères  - à inclure dans le résultat de la recherche

<SearchDBsFlag> recherche dans les bases ? Y ou N

<SearchJobsFlag> recherche dans les Jobs ? Y ou N

<SearchSSISFlag> recherche dans les packages SSIS ? Y ou N