- ------------------------------------------------------------------------------------------------------
- CREATE PROCEDURE dbo.SP_System_RebuildAllIndexes
- /*
- Permet de reconstruire les Indexes
- de toutes les bases du serveur SQL Server
- */
-
- AS
-
- DECLARE @name sysname
- DECLARE @nameTable 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 STATISTIQUES 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')
-
- OPEN TESTCURSEUR
- FETCH NEXT FROM TESTCURSEUR
- INTO @name
-
- WHILE @@FETCH_STATUS = 0
-
- BEGIN
- PRINT ''
- PRINT '---------------------------------------------------------------------'
- PRINT ' RECONSTRUCTION DES INDEXES DE LA BASE : '+ @name
- PRINT '---------------------------------------------------------------------'
-
- SET @LaRequette = 'SELECT ''[''+ U.name +''].[''+ O.name +'']'' AS name
- FROM ['+ @name +'].dbo.sysobjects O INNER JOIN ['+ @name +'].dbo.sysusers U
- ON O.uid = U.uid WHERE O.xtype= ''U'' OR O.xtype= ''V''
- AND O.name NOT IN (''syssegments'', ''sysconstraints'')
- AND NOT(O.name LIKE ''%#%'') ;'
-
- PRINT 'Changement de base : '+ @LaRequette
- PRINT '---------------------------------------------------------------------'
-
- EXEC('DECLARE TESTCURSEURTABLE CURSOR FOR '+ @LaRequette)
- OPEN TESTCURSEURTABLE
- FETCH NEXT FROM TESTCURSEURTABLE
- INTO @nameTable
-
- WHILE @@FETCH_STATUS = 0
- BEGIN
- SET @LaRequette = 'DBCC DBREINDEX (''['+ @name +'].'+ @nameTable +''', '''', 100)'
- PRINT 'Requette : '+ @LaRequette
- EXECUTE (@LaRequette)
- PRINT '---------------------------------------------------------------------'
- FETCH NEXT FROM TESTCURSEURTABLE
- INTO @nameTable
- END
-
- CLOSE TESTCURSEURTABLE
- DEALLOCATE TESTCURSEURTABLE
-
- FETCH NEXT FROM TESTCURSEUR
- INTO @name
- END
-
- PRINT '---------------------------------------------------------------------'
- PRINT ' FIN DE LA RECONSTRUCTION'
- PRINT '---------------------------------------------------------------------'
-
- CLOSE TESTCURSEUR
- DEALLOCATE TESTCURSEUR
- GO
-
- ------------------------------------------------------------------------------------------------------
-
- -- Elle est utilisable avec la commande suivante :
- exec dbo.SP_System_RebuildAllIndexes
-
------------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.SP_System_RebuildAllIndexes
/*
Permet de reconstruire les Indexes
de toutes les bases du serveur SQL Server
*/
AS
DECLARE @name sysname
DECLARE @nameTable 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 STATISTIQUES 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')
OPEN TESTCURSEUR
FETCH NEXT FROM TESTCURSEUR
INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ''
PRINT '---------------------------------------------------------------------'
PRINT ' RECONSTRUCTION DES INDEXES DE LA BASE : '+ @name
PRINT '---------------------------------------------------------------------'
SET @LaRequette = 'SELECT ''[''+ U.name +''].[''+ O.name +'']'' AS name
FROM ['+ @name +'].dbo.sysobjects O INNER JOIN ['+ @name +'].dbo.sysusers U
ON O.uid = U.uid WHERE O.xtype= ''U'' OR O.xtype= ''V''
AND O.name NOT IN (''syssegments'', ''sysconstraints'')
AND NOT(O.name LIKE ''%#%'') ;'
PRINT 'Changement de base : '+ @LaRequette
PRINT '---------------------------------------------------------------------'
EXEC('DECLARE TESTCURSEURTABLE CURSOR FOR '+ @LaRequette)
OPEN TESTCURSEURTABLE
FETCH NEXT FROM TESTCURSEURTABLE
INTO @nameTable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @LaRequette = 'DBCC DBREINDEX (''['+ @name +'].'+ @nameTable +''', '''', 100)'
PRINT 'Requette : '+ @LaRequette
EXECUTE (@LaRequette)
PRINT '---------------------------------------------------------------------'
FETCH NEXT FROM TESTCURSEURTABLE
INTO @nameTable
END
CLOSE TESTCURSEURTABLE
DEALLOCATE TESTCURSEURTABLE
FETCH NEXT FROM TESTCURSEUR
INTO @name
END
PRINT '---------------------------------------------------------------------'
PRINT ' FIN DE LA RECONSTRUCTION'
PRINT '---------------------------------------------------------------------'
CLOSE TESTCURSEUR
DEALLOCATE TESTCURSEUR
GO
------------------------------------------------------------------------------------------------------
-- Elle est utilisable avec la commande suivante :
exec dbo.SP_System_RebuildAllIndexes