SQL SERVER - PROCÉDURE STOCKÉE PERMETTANT DE DEFRAGMENTER TOUS LES INDEXES DE TOUTES LES BASES DU SERVEUR dbcc, indexes, défragmentation, Source N°36635

Information sur la source

Catégorie :Divers Classé sous : dbcc, indexes, défragmentation Niveau : Expert Date de création : 20/03/2006 Date de mise à jour : 03/08/2007 10:58:05 Vu : 3 511

Auteur : fabrice69

Site perso

Ecrire un message privé

Ce membre participe au partage de revenus publicitaires

Note :

10,00 / 10

1
2
3
4
5
6
7
8
9
10

Commentaire sur cette source (0)

Ajouter un commentaire et/ou une note

Description

Comme la source précédente :
- http://sqlfr.com/code.aspx?ID=35836

Il y a des cas (system 24*7 par exemple), où la reconstruction des indexes est impossible. En effet, cette reconstruction commence par la suppression des indexes existant. Ainsi, cela peut pénaliser créellement les utilisateurs à ce moment.
Il est donc plus simple de faire une défragmentation des Indexes existant.

Voila donc la Procédure stockée faisant cela.

Source

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.SP_System_DefragdAllIndexes
/*
Permet de défragmentation des Indexes
de toutes les bases du serveur SQL Server
*/
AS
DECLARE @name sysname
DECLARE @Objectname sysname
DECLARE @Username sysname
DECLARE @IndexName sysname
DECLARE @LaRequette varchar(8000)
DECLARE @DateJour varchar(20)
SET @DateJour = REPLACE(CONVERT(VARCHAR, GetDate(), 102), '.', '_')
PRINT '---------------------------------------------------------------------'
PRINT ' DATE DE LA MISE A JOUR DES INDEXES LANCEE : '+ @DateJour
PRINT '---------------------------------------------------------------------'
DECLARE TESTCURSEUR CURSOR
FOR SELECT Master.dbo.sysdatabases.name FROM Master.dbo.sysdatabases
WHERE Master.dbo.sysdatabases.name NOT IN ('tempdb', 'model', 'pubs', 'master', 'msdb', 'LVM')
ORDER BY Master.dbo.sysdatabases.name
OPEN TESTCURSEUR
FETCH NEXT FROM TESTCURSEUR
INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ''
PRINT '---------------------------------------------------------------------'
PRINT ' DEFRAGMENTATION DES INDEXES DE LA BASE : '+ @name
PRINT '---------------------------------------------------------------------'
SET @LaRequette = 'SELECT USR.name AS UserName, O.name AS Objectname, IDX.name IndexName
FROM '+ @name +'.dbo.sysobjects O INNER JOIN '+ @name +'.dbo.sysusers USR
ON O.uid = USR.uid INNER JOIN '+ @name +'.dbo.sysindexes IDX
ON O.id = IDX.id WHERE (O.xtype= ''U'' OR O.xtype= ''V'')
AND NOT (IDX.keys IS NULL) AND NOT (IDX.status & 64 = 64);'
PRINT 'Changement de base : '+ @LaRequette
PRINT '---------------------------------------------------------------------'
EXEC('DECLARE TESTCURSEURTABLE CURSOR FOR '+ @LaRequette)
OPEN TESTCURSEURTABLE
FETCH NEXT FROM TESTCURSEURTABLE
INTO @Username, @Objectname, @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @LaRequette = 'DBCC INDEXDEFRAG ('''+ @name +''', '''+ @Username+'.'+ @Objectname +''', '''+ @IndexName +''')'
PRINT 'Requette : '+ @LaRequette
EXECUTE (@LaRequette)
PRINT '---------------------------------------------------------------------'
FETCH NEXT FROM TESTCURSEURTABLE
INTO @Username, @Objectname, @IndexName
END
CLOSE TESTCURSEURTABLE
DEALLOCATE TESTCURSEURTABLE
SET @LaRequette = 'USE '+ @name +' exec sp_updatestats'
PRINT 'Requette : '+ @LaRequette
EXECUTE (@LaRequette)
FETCH NEXT FROM TESTCURSEUR
INTO @name
END
PRINT '---------------------------------------------------------------------'
PRINT ' FIN DE LA DEFRAGMENTATION
PRINT '---------------------------------------------------------------------'
CLOSE TESTCURSEUR
DEALLOCATE TESTCURSEUR
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER   PROCEDURE dbo.SP_System_DefragdAllIndexes 
/* 
    Permet de défragmentation des Indexes 
     de toutes les bases du serveur SQL Server 
*/ 
   
AS 
  
DECLARE @name sysname 
DECLARE @Objectname sysname
DECLARE @Username sysname
DECLARE @IndexName sysname
DECLARE @LaRequette varchar(8000) 
DECLARE @DateJour varchar(20) 
   
SET @DateJour = REPLACE(CONVERT(VARCHAR, GetDate(), 102), '.', '_') 
PRINT '---------------------------------------------------------------------' 
PRINT ' DATE DE LA MISE A JOUR DES INDEXES LANCEE : '+ @DateJour 
PRINT '---------------------------------------------------------------------' 
   
DECLARE TESTCURSEUR CURSOR 
   FOR SELECT Master.dbo.sysdatabases.name FROM Master.dbo.sysdatabases 
       WHERE Master.dbo.sysdatabases.name NOT IN ('tempdb', 'model', 'pubs', 'master', 'msdb', 'LVM') 
	ORDER BY Master.dbo.sysdatabases.name
   
OPEN TESTCURSEUR 
FETCH NEXT FROM TESTCURSEUR 
INTO @name 
   
WHILE @@FETCH_STATUS = 0 
   
BEGIN 
    PRINT '' 
    PRINT '---------------------------------------------------------------------' 
    PRINT ' DEFRAGMENTATION DES INDEXES DE LA BASE : '+ @name 
    PRINT '---------------------------------------------------------------------' 
  
    SET @LaRequette = 'SELECT USR.name AS UserName, O.name AS Objectname, IDX.name IndexName
            FROM '+ @name +'.dbo.sysobjects O INNER JOIN '+ @name +'.dbo.sysusers USR 
		ON O.uid = USR.uid INNER JOIN '+ @name +'.dbo.sysindexes IDX
	    ON O.id = IDX.id WHERE (O.xtype= ''U'' OR O.xtype= ''V'')
	    AND NOT (IDX.keys IS NULL) AND NOT (IDX.status & 64 = 64);' 
  
    PRINT 'Changement de base : '+ @LaRequette 
    PRINT '---------------------------------------------------------------------' 
  
    EXEC('DECLARE TESTCURSEURTABLE CURSOR FOR '+ @LaRequette) 
    OPEN TESTCURSEURTABLE 
    FETCH NEXT FROM TESTCURSEURTABLE 
    INTO @Username, @Objectname, @IndexName 
   
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
        SET @LaRequette = 'DBCC INDEXDEFRAG ('''+ @name +''', '''+ @Username+'.'+ @Objectname +''', '''+ @IndexName +''')' 
        PRINT 'Requette : '+ @LaRequette 
        EXECUTE (@LaRequette) 
        PRINT '---------------------------------------------------------------------' 
        FETCH NEXT FROM TESTCURSEURTABLE 
        INTO @Username, @Objectname, @IndexName
    END     
  
    CLOSE TESTCURSEURTABLE 
    DEALLOCATE TESTCURSEURTABLE 

    SET @LaRequette = 'USE '+ @name +' exec sp_updatestats' 
    PRINT 'Requette : '+ @LaRequette 
    EXECUTE (@LaRequette) 
  
    FETCH NEXT FROM TESTCURSEUR 
    INTO @name 
END 
  
PRINT '---------------------------------------------------------------------' 
PRINT ' FIN DE LA DEFRAGMENTATION 
PRINT '---------------------------------------------------------------------' 
  
CLOSE TESTCURSEUR 
DEALLOCATE TESTCURSEUR 


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Conclusion

En espérant que cette SP vous rende service.

Bon coding

Romelard Fabrice (Alias F___)

Historique

20 mars 2006 20:27:57 :: Mise a jour en ajoutant un filtre sur les indexes de statistiques. Ajout de la mise a jour des stats en fin d'exécution. Remerciement à Frédéric BROUARD pour cette aide.
19 avril 2006 00:02:04 :: Correction du titre
03 août 2007 10:58:05 :: Mise à jour pour gestion des objets avec user spécifique