begin process at 2012 02 04 17:25:32
  Trouver un code source :
 
dans
 
Accueil > 

Code

 > 

Divers

 > SQL SERVER - PROCÉDURE STOCKÉE PERMETTANT DE DEFRAGMENTER TOUS LES INDEXES DE TOUTES LES BASES DU SERVEUR

SQL SERVER - PROCÉDURE STOCKÉE PERMETTANT DE DEFRAGMENTER TOUS LES INDEXES DE TOUTES LES BASES DU SERVEUR


 Information sur la source

Note :
10 / 10 - par 2 personnes
10,00 / 10

  • 1

  • 2

  • 3

  • 4

  • 5

  • 6

  • 7

  • 8

  • 9

  • 10
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 :7 487

Auteur : fabrice69

Ecrire un message privé
Site perso
Ce membre participe au partage de revenus publicitaires
Commentaire sur cette source (1)
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

 Sources du même auteur

SQL SERVER - OBTENIR LES VALEURS CORRESPONDANT A DES UNICODE
SQL SERVER 2000 - VIDER ET COMPACTER TOUTES LES BASES DE DON...
SQL SERVER 2005 : OBTENIR LA LISTE DES BASES DE CONTENU D'UN...
SQL SERVER - OBTENIR LA POSITION DU PREMIER CHIFFRE D'UNE CH...
SQL SERVER - UPDATE D'UNE TABLE EN BOUCLE PAR LOT

 Sources de la même categorie

Source avec Zip TUTORIEL PL/SQL par Julien39
CAPTURE DES CHANGEMENTS DE DONNÉES (CDC: CHANGE DATA CAPTURE... par wissemhabboub
INTERPRETEUR BRAINFUCK par coucou747
CRÉATION D'UNE BASE DE DONNÉE AVEC FICHIER DE DONNÉES, JOURN... par fearmaker
MYSQL : CRÉATION D'UN GUID par maitredede

 Sources en rapport avec celle ci

RÉINDEXER TOUTES LES TABLES D'UNE BASE DE DONNÉES par pneau
SQL SERVER 2000 - CREER DES INDEXES SUR UN CHAMP EXISTANT DA... par fabrice69
SQL SERVER 2005 - LISTER LES INDEXES AVEC LE NOMBRE DE LIGNE... par fabrice69
SQL SERVER 2005 - DETRUIRE TOUS LES INDEXES APPLIQUES SUR UN... par fabrice69
SQL SERVER - PROCÉDURE STOCKÉE PERMETTANT DE RECONSTRUIRE TO... par fabrice69

Commentaires et avis

Commentaire de pouda le 07/06/2010 12:02:45 10/10

Bonjour,

Votre code me sauve la vie aujourd'hui. Merci beaucoup. Je vous met 10/10

 Ajouter un commentaire




Nos sponsors


Sondage...

CalendriCode

Février 2012
LMMJVSD
  12345
6789101112
13141516171819
20212223242526
272829    

Consulter la suite du CalendriCode

Photothèque

 
Développement réalisé par Nicolas SOREL (Nix) avec l'aide de : Cyril DURAND et Emmanuel (EBArtSoft), Merci à Vincent pour ses précieux conseils.
CodeS-SourceS.com© Toute reproduction même partielle est interdite sauf accord écrit du Webmaster
CodeS-SourceS.com© est une marque déposée tous droits réservés

Google Coop CodeS-SourceS Google Coop CodeS-SourceS
Temps d'éxécution de la page : 1,716 sec (4)

Nous contacter | Annoncer sur CodeS-SourceS | Mentions légales