Rebuild all indexes for all tables and all databases

Rédigé par Sozezzo - - Aucun commentaire

Maintain database indexes

You can just create script or run right now the reindex.
Verify the configuration to filter databases or to change the FILLFACTOR.


PRINT '-- ***************************************** --'
PRINT '-- Reindex all tables'
PRINT '-- on selected databases'
PRINT '-- ***************************************** --'
PRINT '--'
PRINT '/*'
PRINT @@servername;
PRINT Getdate();
PRINT '*/'
SET NOCOUNT ON;

-- CONFIGURATION
DECLARE @runScriptRightNow INT = 0;
DECLARE @fillfactor AS NVARCHAR(10) = '99' -- occuped% -- https://docs.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index
BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH;
SELECT [name] INTO #dbs FROM sys.databases
WHERE database_id > 4
AND name NOT IN ('MyDatabaseNoReindex')
ORDER BY [name]

-- CONFIGURATION

USE [MASTER];

DECLARE @dbname nvarchar(200);
DECLARE @sql NVARCHAR(MAX);
DECLARE @ToKill AS NVARCHAR(MAX);

DECLARE @templateSelectTable AS NVARCHAR(MAX) = 'SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName FROM [$(dbname)].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' 
DECLARE @templateReindex AS NVARCHAR(MAX) ='ALTER INDEX ALL ON $(tablename) REBUILD WITH (FILLFACTOR = $(fillfactor));'
SET @templateReindex = REPLACE(@templateReindex,'$(fillfactor)',@fillfactor)
 
DECLARE @table AS table (TableName nvarchar(256))
DECLARE @script AS table (Idx int identity,SqlScript nvarchar(max))
DECLARE @Idx int;
DECLARE @SqlScript nvarchar(max)
WHILE (EXISTS(SELECT * FROM #dbs))
BEGIN

    SELECT TOP 1 @dbname = name FROM #dbs;
    DELETE FROM #dbs     WHERE name = @dbname;

 SET @sql = REPLACE(@templateSelectTable,'$(dbname)',@dbname);
 DELETE FROM @table;
 INSERT INTO @table (TableName)
 EXEC(@sql)

 INSERT INTO  @script(SqlScript)
 select REPLACE(@templateReindex,'$(tablename)',TableName) from  @table;

 WHILE (EXISTS(SELECT * FROM @script))
 BEGIN
  SELECT TOP 1 @Idx = Idx, @SqlScript = SqlScript FROM @script t;
  DELETE FROM @script WHERE Idx = @Idx;
  
  IF (@runScriptRightNow = 1)
  BEGIN
   RAISERROR (@SqlScript, 10, 1) WITH NOWAIT;
   EXEC (@SqlScript);
  END
  ELSE
  PRINT @SqlScript
 END

END

 

Same code with safe mode and try again


GO
PRINT '-- ***************************************** --'
PRINT '-- Reindex all tables with safe mode'
PRINT '-- on selected databases'
PRINT '-- ***************************************** --'
PRINT '--'
PRINT '-- ' +@@servername;
PRINT '-- ' +cast(Getdate() as nvarchar(100));
SET NOCOUNT ON;

-- CONFIGURATION
DECLARE @runScriptRightNow INT = 1;
DECLARE @fillfactor AS NVARCHAR(10) = '98' -- occuped% -- https://docs.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index

BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH;
SELECT [name] INTO #dbs FROM sys.databases
WHERE database_id > 4
AND name   IN ('MyDatabaseNoReindex')
ORDER BY [name]

-- CONFIGURATION$(ESCAPE_NONE(dbname))

USE [MASTER];

DECLARE @dbname nvarchar(200);
DECLARE @sql NVARCHAR(MAX);
DECLARE @ToKill AS NVARCHAR(MAX);

DECLARE @templateSelectTable AS NVARCHAR(MAX) = 'SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName FROM [[#dbname]].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''  
DECLARE @templateReindex AS NVARCHAR(MAX) ='
DECLARE @tryAgain INT = 2;
WHILE (@tryAgain>0)
BEGIN
    BEGIN TRY
        PRINT ''-- REINDEX Table  : [#tablename]'';
        ALTER INDEX ALL ON [#tablename] REBUILD WITH (FILLFACTOR = [#fillfactor]));
    END TRY
    BEGIN CATCH
        SET @tryAgain = @tryAgain - 1;
        WAITFOR DELAY ''00:00:10'';
        CONTINUE;
    END CATCH
    BREAK;
END;
'
SET @templateReindex = REPLACE(@templateReindex,'[#fillfactor])',@fillfactor)
 
DECLARE @table AS table (TableName nvarchar(256))
DECLARE @script AS table (Idx int identity,SqlScript nvarchar(max))
DECLARE @Idx int;
DECLARE @SqlScript nvarchar(max)
WHILE (EXISTS(SELECT * FROM #dbs))
BEGIN

    SELECT TOP 1 @dbname = name FROM #dbs;
    DELETE FROM #dbs     WHERE name = @dbname;

    SET @sql = REPLACE(@templateSelectTable,'[#dbname]',@dbname);
    DELETE FROM @table;
    INSERT INTO @table (TableName)
    EXEC(@sql)

    INSERT INTO  @script(SqlScript)
    select REPLACE(@templateReindex,'[#tablename]',TableName) from  @table;

    WHILE (EXISTS(SELECT * FROM @script))
    BEGIN
        SELECT TOP 1 @Idx = Idx, @SqlScript = SqlScript FROM @script t;
        DELETE FROM @script WHERE Idx = @Idx;
        
        IF (@runScriptRightNow = 1)
        BEGIN
            RAISERROR (@SqlScript, 10, 1) WITH NOWAIT;
            RAISERROR ('GO', 10, 1) WITH NOWAIT;
            EXEC (@SqlScript);
        END
        ELSE
        PRINT @SqlScript
    END

END
GO

 

Sources :

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index
https://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/

 

Les commentaires sont fermés.