[SQL Server] Exécuter un fichier de commandes SQL

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

-- Author:      KHARROUBI Zouhaier

-- Create date: 03/03/2019

-- Description: Exécuter un fichier de commandes SQL

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

DECLARE      @Instance_SQLServerASVARCHAR(150)

             ,@Nom_Base                        ASVARCHAR(150)

             ,@Login                                  ASVARCHAR(35)=NULL

             ,@PassWord                        ASVARCHAR(35)=NULL

             ,@Chemin_ScriptsSQL        ASVARCHAR(MAX)

             ,@ExecScript               ASBIT= 1          -- 0 = Ne pas exécuter les scripts. Juste afficher la liste des scripts à exécuter. 1 = Exécuter les scripts

             ,@OutilTraitement          ASCHAR(1)='S'      -- P = PowerShell, Via PowerShel la commande n'est pas encore fiable. A éviter pour l'instant, S = sqlcmd

             ,@GenererBackup                   ASBIT=0

             --Exemple d'utilisation

             SELECT@Instance_SQLServer='MonInstanceSQLServer'

                           ,@Nom_Base                 ='MaBase'

                           ,@Login                           ='MonLogin'

                           ,@PassWord                 ='MonPassWord'

                           ,@Chemin_ScriptsSQL='\\MonServeurFichiers\MesScripts\Admin\'

                           ,@ExecScript        = 1

                           ,@OutilTraitement   ='S'

                           ,@GenererBackup            =0

BEGIN

       -- SET NOCOUNT ON added to prevent extra result sets from

       -- interfering with SELECT statements.

       SETNOCOUNTON;

   -- Insert statements for procedure here

       IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#ListeFichiersSQL]'))

             BEGIN

                    DROPTABLE #ListeFichiersSQL

             END

       IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#ListeFichiersRPT]'))

             BEGIN

                    DROPTABLE #ListeFichiersRPT

             END

       IFEXISTS(SELECT*FROM tempdb..sysobjectsWHERE id =object_id(N'[tempdb]..[#Journal]'))

             BEGIN

                    DROPTABLE #Journal

             END

       CREATETABLE #ListeFichiersSQL

       (

             Num_Lig                                  INTIDENTITY (1,1),

             ExecFichierSQL                    BIT                               NULLDEFAULT 1,    

             DateDebExec                       DATETIME                   NULL,

             DateFinExec                       DATETIME                   NULL,

             NomFichierSQL              VARCHAR(800)        NULL,

             NomFichierRPT              VARCHAR(800)        NULL,

             EtatDeploiement                   VARCHAR(50)                DEFAULT'Script non déployé',

             MessageErreur              VARCHAR(MAX)        NULL

       )

       CREATENONCLUSTEREDINDEX [IDX_#ListeFichiersSQL_NomFichierRPT] ON #ListeFichiersSQL

       (

             NomFichierRPT ASC

       )

       CREATETABLE #ListeFichiersRPT

       (

             NomFichierRPT              VARCHAR(800)NULL

       )

       CREATENONCLUSTEREDINDEX [IDX_#ListeFichiersRPT_NomFichierRPT] ON #ListeFichiersRPT

       (

             NomFichierRPT ASC

       )

       CREATETABLE #Journal

       (

       Num_Lig                                 INTIDENTITY (1,1),

       LigneJournal               VARCHAR(MAX)

       )

       DECLARE @Requete                              NVARCHAR(3000)

                    ,@FichierSQL                      VARCHAR(800)

                    ,@NomFichierSQL                         VARCHAR(800)

                    ,@NomFichierRPT                         VARCHAR(800)

                    ,@NomCompletFichierSQL            VARCHAR(800)

                    ,@NomFichierRetour                VARCHAR(800)

                    ,@Nbre_Lig                              INT

                    ,@Num_Lig                               INT

                    ,@ExecFichierSQL                  BIT

                    ,@MessageErreur                         VARCHAR(MAX)

                    ,@DateDebExec                     DATETIME                  

                    ,@DateFinExec                     DATETIME

                    ,@LocalHot                              VARCHAR(50)

                    ,@RetRequete                      INT

       SELECT @LocalHot =QUOTENAME(@@SERVERNAME)

       IFCHARINDEX('\',REVERSE(@Chemin_ScriptsSQL),1)<>1

             BEGIN

                    SELECT @Chemin_ScriptsSQL = @Chemin_ScriptsSQL +'\'

             END

            

       --Récupérer la liste des fichiers RPT

       SELECT @Requete =N'DIR "'+ @Chemin_ScriptsSQL +'*.rpt" /B'

       INSERTINTO #ListeFichiersRPT(NomFichierRPT)

       EXECMaster..xp_cmdshell@Requete

       --Générer le fichier du backup

       IF @GenererBackup=1

             BEGIN

                    SELECT @Requete ='SELECT Command FROM (

                    SELECT TOP 1 1 AS NumCommand,Command FROM [msdb].[dbo].[sysjobsteps]

                    WHERE command LIKE ''''%BACKUP_DIFF%''''

                    UNION

                    SELECT 2 AS NumCommand,''''GO'''' AS Command

                    UNION

                    SELECT TOP 1 3 AS NumCommand,Command FROM [msdb].[dbo].[sysjobsteps]

                    WHERE command LIKE ''''%BACKUP_LOG%'''''+' AND (SELECT COUNT(recovery_model) FROM [master].[sys].[databases] WHERE recovery_model=1 AND [name]='''''+ @Nom_Base +''''')=1'

                    +' UNION

                    SELECT 4 AS NumCommand,''''GO'''' AS Command FROM [msdb].[dbo].[sysjobsteps]

                    WHERE command LIKE ''''%BACKUP_LOG%'''''+' AND (SELECT COUNT(recovery_model) FROM [master].[sys].[databases] WHERE recovery_model=1 AND [name]='''''+ @Nom_Base +''''')=1'

                    +') T1 ORDER BY NumCommand'

                    --Forcer le backup FULL

                    --SELECT @Requete = 'SELECT Command FROM (

                    --SELECT TOP 1 1 AS NumCommand,Command FROM [msdb].[dbo].[sysjobsteps]

                    --WHERE command LIKE ''''%BACKUP_FULL%''''

                    --UNION

                    --SELECT 2 AS NumCommand,''''GO'''' AS Command

                    --) T1 ORDER BY NumCommand'

                    SELECT @Requete =CASEWHEN @LocalHot<>@Instance_SQLServer THENN'SELECT Command FROM OPENQUERY('+QUOTENAME(@Instance_SQLServer)+','''ELSE''END+ @Requete

                    SELECT @Requete =CASEWHEN @LocalHot<>@Instance_SQLServer THEN @Requete +N''')'ELSEREPLACE(@Requete,'''''','''')END

            

                    INSERTINTO #Journal(LigneJournal)

                    EXECUTEsp_executesql@Requete

                    UPDATE #Journal

                    SET

                           LigneJournal='exec master..MaProcedureBackupDB '+SUBSTRING(LigneJournal,CHARINDEX('''',LigneJournal),LEN(LigneJournal)-CHARINDEX('''',LigneJournal))+','''+ @Nom_Base +''';'

                    WHERE LigneJournal<>'GO'

                    SELECT @Num_Lig = 1,@Nbre_Lig=COUNT(LigneJournal), @NomFichierRetour='"'+ @Chemin_ScriptsSQL +'0-ScriptBackup'+ @Nom_Base +'.sql"'FROM #Journal

                    WHILE @Num_Lig <= @Nbre_Lig

                           BEGIN

                                  SELECT @MessageErreur=LigneJournal FROM #Journal WHERE Num_Lig=@Num_Lig

                                  IF @Num_Lig=1

                                        BEGIN

                                               SELECT @Requete ='ECHO '+ @MessageErreur +' > '+ @NomFichierRetour

                                        END

                                  ELSE

                                        BEGIN

                                               SELECT @Requete ='ECHO '+ @MessageErreur +' >> '+ @NomFichierRetour

                                        END

                                  EXECMaster..xp_cmdshell@Requete

                                  SELECT @Num_Lig = @Num_Lig + 1

                           END

                   

             END

       --Récupérer la liste des scripts SQL          

       SELECT @Requete =N'DIR "'+ @Chemin_ScriptsSQL +'*.sql" /B'

            

       INSERTINTO #ListeFichiersSQL(NomFichierSQL)

   EXECMaster..xp_cmdshell@Requete

       UPDATE #ListeFichiersSQL

       SET

             NomFichierRPT =SUBSTRING(NomFichierSQL,1,LEN(NomFichierSQL)-3)+'rpt'

       --Activer les scripts à lancer

       UPDATE T1

       SET

             ExecFichierSQL=0

       FROM #ListeFichiersSQL T1

       JOIN #ListeFichiersRPT T2 ON T2.NomFichierRPT=T1.NomFichierRPT

       SELECT @Num_Lig = 1,@Nbre_Lig=COUNT(*)FROM #ListeFichiersSQL

       WHILE @Num_Lig <= @Nbre_Lig AND @ExecScript=1

             BEGIN

                    SELECT @NomFichierSQL= NomFichierSQL,@NomFichierRPT=NomFichierRPT,@ExecFichierSQL=ExecFichierSQL FROM #ListeFichiersSQL WHERE Num_Lig=@Num_Lig

                    IF @NomFichierSQL ISNOTNULLAND @NomFichierRPT ISNOTNULLAND @ExecFichierSQL=1

                           BEGIN

                                  SELECT @NomCompletFichierSQL ='"'+ @Chemin_ScriptsSQL + @NomFichierSQL +'"'

                                               ,@NomFichierRetour ='"'+ @Chemin_ScriptsSQL + @NomFichierRPT +'"'

                                  SELECT @DateDebExec=GETDATE()

                                              

                                  IF @OutilTraitement='P'--Faire le traitement avec PowerShell

                                        BEGIN

                                               SELECT @Requete ='PowerShell.exe invoke-sqlcmd -inputfile '+ @NomCompletFichierSQL +' -serverinstance "'+ @Instance_SQLServer +'" -database "'+ @Nom_Base +'" | Out-File -filePath '+ @NomFichierRetour

                                               --SELECT @Requete = 'PowerShell.exe invoke-sqlcmd -inputfile ' + @NomCompletFichierSQL + ' -serverinstance "' + @Instance_SQLServer + '" -database "' + @Nom_Base + '" -ErrorAction Stop -OutputSqlErrors True -Verbose > ' + @NomFichierRetour

                                        END

                                  ELSE

                                        --Faire le traitement avec sqlcmd

                                        BEGIN

                                               IF ISNULL(@Login,'')=''ORISNULL(@PassWord,'')=''

                                                      BEGIN

                                                             SELECT @Requete ='sqlcmd -I -u -E -S '+ @Instance_SQLServer CASE WHEN @NomFichierSQL LIKE'%ScriptBackup%'THEN' -m0 'ELSE' -m-1 'END+' -d '+ @Nom_Base +' -i '+ @NomCompletFichierSQL +' -r0 -b -o '+ @NomFichierRetour

                                                      END

                                               ELSE

                                                      BEGIN

                                                             SELECT @Requete ='sqlcmd -I -u -U '+ @Login +' -P '+ @PassWord +' -S '+ @Instance_SQLServer +CASEWHEN @NomFichierSQL LIKE'%ScriptBackup%'THEN' -m0 'ELSE' -m-1 'END+' -d '+ @Nom_Base +' -i '+ @NomCompletFichierSQL +' -r0 -b -o '+ @NomFichierRetour

                                                      END

                                        END

                                  INSERTINTO #Journal(LigneJournal)

                                  EXECMaster..xp_cmdshell@Requete

                                  SELECT @DateFinExec=GETDATE()

                                  -- Intercepter le message d'ereur

                                  SELECT @Requete ='TYPE '+ @NomFichierRetour

                                 

                                  TRUNCATETABLE #Journal

                                  INSERTINTO #Journal(LigneJournal)

                                  EXECMaster..xp_cmdshell@Requete

                                  SELECT @MessageErreur =''

                                  SELECT @MessageErreur=@MessageErreur +' '+ISNULL(LigneJournal,'')FROM #Journal

                                  UPDATE #ListeFichiersSQL

                                  SET

                                        MessageErreur= @MessageErreur

                                        ,DateDebExec=@DateDebExec

                                        ,DateFinExec=@DateFinExec

                                  WHERE Num_Lig=@Num_Lig

            

                                  IFEXISTS(SELECT LigneJournal FROM #Journal WHERE LigneJournal LIKE'%level_[1-9]%'OR LigneJournal LIKE'%Niveau_[1-9]%'

                                  OR LigneJournal LIKE'%msg_[1-9]%'OR LigneJournal LIKE'%Error%'OR LigneJournal LIKE'%Erreur%'OR LigneJournal LIKE'%cannot%')

                                  OR(@NomFichierSQL LIKE'%retour%arriere%'ANDNOTEXISTS(SELECT LigneJournal FROM #Journal WHERE LigneJournal LIKE+'%'+ @Nom_Base +'%.bak%'))

                                        BEGIN

                                               --Arrêter le déploiement en cas d'erreur

                                               UPDATE #ListeFichiersSQL

                                               SET

                                                      EtatDeploiement='Echec déploiement'    

                                               WHERE Num_Lig=@Num_Lig

                                                            

                                               SELECT @Num_Lig = @Nbre_Lig

                                        END

                                  ELSE

                                        BEGIN

                                               UPDATE #ListeFichiersSQL

                                               SET

                                                      EtatDeploiement='Succès déploiement'   

                                               WHERE Num_Lig=@Num_Lig

                                        END

                                  IF (SELECTCOUNT(*)FROM #Journal)=1 ANDEXISTS(SELECT LigneJournal FROM #Journal WHERE LigneJournal ISNULL)

                                        BEGIN

                                               --SELECT @Requete = 'ECHO Commandes reussies >>' + @NomFichierRetour

                                               --EXEC Master..xp_cmdshell @Requete

                                               IFISNULL(@Login,'')=''ORISNULL(@PassWord,'')=''

                                                      BEGIN

                                                             SELECT @Requete ='sqlcmd -I -u -E -S '+ @Instance_SQLServer +CASEWHEN @NomFichierSQL LIKE'%retour%arriere%'THEN' -m0 'ELSE' -m-1 'END+' -d '+ @Nom_Base +' -q "PRINT ''Commandes réussies''"'+' -r0 -b -o '+ @NomFichierRetour

                                                      END

                                               ELSE

                                                      BEGIN

                                                             SELECT @Requete ='sqlcmd -I -u -U '+ @Login +' -P '+ @PassWord +' -S '+ @Instance_SQLServer +CASEWHEN @NomFichierSQL LIKE'%retour%arriere%'THEN' -m0 'ELSE' -m-1 'END+' -d '+ @Nom_Base +' -q "PRINT ''Commandes réussies''" '+' -r0 -b -o '+ @NomFichierRetour

                                                      END

                                               INSERT INTO #Journal(LigneJournal)

                                               EXECMaster..xp_cmdshell@Requete

                                               UPDATE #ListeFichiersSQL

                                               SET

                                                      MessageErreur='Commandes réussies'

                                               WHERE Num_Lig=@Num_Lig

                                        END

                           END

                    SELECT @Num_Lig = @Num_Lig + 1

             END

       SELECT*FROM #ListeFichiersSQL WHERE NomFichierSQL ISNOTNULL

       --SELECT * FROM #Journal

       --SELECT * FROM #ListeFichiersRPT

       DROPTABLE #ListeFichiersSQL

       DROPTABLE #Journal

       DROPTABLE #ListeFichiersRPT

END