[SQL Server] Trouver une chaine de caractere dans toutes les tables de toute la BD

  • Imprimer

 

-- =============================================

-- Author : KHARROUBI Zouhaier

-- Create date: 23/04/2014

-- Description: Trouver une chaine de caractere dans toutes les tables de toute la BD

-- le script permet de retourner :

-- 1- Le nom de la table et le nom de la colonne où la valeur recherchée a été trouvée

-- 2- La requête SELECT pour interroger la table où la valeur recherchée a été trouvée

-- 3- La requête UPDATE pour mêttre à jour la table où la valeur recherchée a été trouvée

-- =============================================

IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#ListeRequete]'))

DROP TABLE #ListeRequete

IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#ListeResultat]'))

DROP TABLE #ListeResultat

CREATE TABLE #ListeResultat

(

NUM_RESULTAT INT IDENTITY(1,1)

,NOM_TABLE NVARCHAR(MAX) NULL

,NOM_COLONNE NVARCHAR(MAX) NULL

,REQUETE_RECH NVARCHAR(MAX) NULL

,REQUETE_MAJ NVARCHAR(MAX) NULL

,MessageErreur NVARCHAR(MAX) NULL

)

CREATE TABLE #ListeRequete

(

NUM_REQUETE INT IDENTITY(1,1)

,NOM_TABLE NVARCHAR(MAX) NULL

,NOM_COLONNE NVARCHAR(MAX) NULL

,REQUETE_RECH NVARCHAR(MAX) NULL

,REQUETE_MAJ NVARCHAR(MAX) NULL

)

DECLARE @ChaineRecherchee NVARCHAR(MAX)

DECLARE @ChaineRemplacee NVARCHAR(MAX)

DECLARE @REQUETE NVARCHAR(MAX)

DECLARE @ParamDefinition NVARCHAR(500);

DECLARE @Nbre_Lig AS INT

DECLARE @Nbre_LigTrouve AS INT

DECLARE @Indice AS INT

SELECT @ChaineRecherchee = 'should average less than 50'

SELECT @ChaineRemplacee ='should average less than 50'

SELECT @Indice=1

SELECT @ParamDefinition = N'@Nbre_LigTrouveOUT INT OUTPUT';

INSERT INTO #ListeRequete(NOM_TABLE,NOM_COLONNE,REQUETE_RECH,REQUETE_MAJ)

SELECT DISTINCT b.name NOM_TABLE,c.Name NOM_COLONNE,N'SELECT @Nbre_LigTrouveOUT=COUNT(*) FROM ' + QUOTENAME(T.TABLE_SCHEMA) + '.' + QUOTENAME(b.name)+ N' WHERE CAST([' + c.Name + N'] AS NVARCHAR(MAX)) LIKE ''%' + @ChaineRecherchee + '%''' REQUETE_RECH

,N'UPDATE ' + QUOTENAME(T.TABLE_SCHEMA) + '.' + QUOTENAME(b.name) + N' SET [' + c.Name + ']=' +

N'REPLACE(' + c.Name + N',''' + @ChaineRecherchee + N''',''' + @ChaineRemplacee + N''') ' + N' WHERE CAST([' + c.Name + N'] AS NVARCHAR(MAX)) LIKE ''%' + @ChaineRecherchee + '%''' REQUETE_MAJ

FROM

sysobjects b,

syscolumns c,

systypes d,

[INFORMATION_SCHEMA].[TABLES] T

WHERE

C.id = b.id AND c.xtype=d.xtype AND

b.type='u' AND b.Name <> 'dtproperties'
AND T.TABLE_NAME=b.name

--AND(d.collation IS NOT NULL OR d.name='xml')

SELECT @Nbre_Lig=COUNT(*) FROM #ListeRequete

WHILE @Indice<=@Nbre_Lig>

 

 

 

BEGIN

 

 

 

SELECT @REQUETE=REQUETE_RECH,@Nbre_LigTrouve=0 FROM #ListeRequete WHERE NUM_REQUETE=@Indice>

 

 

BEGIN TRY

EXECUTE sp_executesql @REQUETE,@ParamDefinition,@Nbre_LigTrouveOUT=@Nbre_LigTrouve OUTPUT;

IF @Nbre_LigTrouve>0

BEGIN

INSERT INTO #ListeResultat(NOM_TABLE,NOM_COLONNE,REQUETE_RECH,REQUETE_MAJ) SELECT NOM_TABLE,NOM_COLONNE,REQUETE_RECH,REQUETE_MAJ FROM #ListeRequete WHERE NUM_REQUETE=@Indice>

SELECT @Nbre_LigTrouve=0

 

END

END TRY

 

BEGIN CATCH

INSERT INTO #ListeResultat(NOM_TABLE,NOM_COLONNE,REQUETE_RECH,REQUETE_MAJ,MessageErreur) SELECT NOM_TABLE,NOM_COLONNE,REQUETE_RECH,REQUETE_MAJ,ERROR_MESSAGE() FROM #ListeRequete WHERE NUM_REQUETE=@Indice>

END CATCH

 

 

SELECT @Indice=@Indice + 1

END

SELECT NUM_RESULTAT,NOM_TABLE,NOM_COLONNE,REQUETE_RECH,REQUETE_MAJ FROM #ListeResultat WHERE MessageErreur IS NULL

SELECT * FROM #ListeRequete

DROP TABLE #ListeRequete

DROP TABLE #ListeResultat