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
