Rebuild All Indexes for All Tables and All Databases

 1
 2PRINT '-- ***************************************** --'
 3PRINT '-- Reindex all tables'
 4PRINT '-- on selected databases'
 5PRINT '-- ***************************************** --'
 6PRINT '--'
 7PRINT '/*'
 8PRINT @@servername;
 9PRINT Getdate();
10PRINT '*/'
11SET NOCOUNT ON;
12
13-- CONFIGURATION
14DECLARE @runScriptRightNow INT = 0;
15DECLARE @fillfactor AS NVARCHAR(10) = '99' -- occuped% -- <a href="https://docs.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index">https://docs.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index</a>
16BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH;
17SELECT [name] INTO #dbs FROM sys.databases
18WHERE database_id > 4
19AND name NOT IN ('MyDatabaseNoReindex')
20ORDER BY [name]
21
22-- CONFIGURATION
23
24USE [MASTER];
25
26DECLARE @dbname nvarchar(200);
27DECLARE @sql NVARCHAR(MAX);
28DECLARE @ToKill AS NVARCHAR(MAX);
29
30DECLARE @templateSelectTable AS NVARCHAR(MAX) = 'SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName FROM [$(dbname)].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
31DECLARE @templateReindex AS NVARCHAR(MAX) ='ALTER INDEX ALL ON $(tablename) REBUILD WITH (FILLFACTOR = $(fillfactor));'
32SET @templateReindex = REPLACE(@templateReindex,'$(fillfactor)',@fillfactor)
33
34DECLARE @table AS table (TableName nvarchar(256))
35DECLARE @script AS table (Idx int identity,SqlScript nvarchar(max))
36DECLARE @Idx int;
37DECLARE @SqlScript nvarchar(max)
38WHILE (EXISTS(SELECT * FROM #dbs))
39BEGIN
40
41    SELECT TOP 1 @dbname = name FROM #dbs;
42    DELETE FROM #dbs     WHERE name = @dbname;
43
44 SET @sql = REPLACE(@templateSelectTable,'$(dbname)',@dbname);
45 DELETE FROM @table;
46 INSERT INTO @table (TableName)
47 EXEC(@sql)
48
49 INSERT INTO  @script(SqlScript)
50 select REPLACE(@templateReindex,'$(tablename)',TableName) from  @table;
51
52 WHILE (EXISTS(SELECT * FROM @script))
53 BEGIN
54  SELECT TOP 1 @Idx = Idx, @SqlScript = SqlScript FROM @script t;
55  DELETE FROM @script WHERE Idx = @Idx;
56
57  IF (@runScriptRightNow = 1)
58  BEGIN
59   RAISERROR (@SqlScript, 10, 1) WITH NOWAIT;
60   EXEC (@SqlScript);
61  END
62  ELSE
63  PRINT @SqlScript
64 END
65
66END

 1
 2GO
 3PRINT '-- ***************************************** --'
 4PRINT '-- Reindex all tables with safe mode'
 5PRINT '-- on selected databases'
 6PRINT '-- ***************************************** --'
 7PRINT '--'
 8PRINT '-- ' +@@servername;
 9PRINT '-- ' +cast(Getdate() as nvarchar(100));
10SET NOCOUNT ON;
11
12-- CONFIGURATION
13DECLARE @runScriptRightNow INT = 1;
14DECLARE @fillfactor AS NVARCHAR(10) = '98' -- occuped% -- https://docs.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index
15
16BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH;
17SELECT [name] INTO #dbs FROM sys.databases
18WHERE database_id > 4
19AND name   IN ('MyDatabaseNoReindex')
20ORDER BY [name]
21
22-- CONFIGURATION$(ESCAPE_NONE(dbname))
23
24USE [MASTER];
25
26DECLARE @dbname nvarchar(200);
27DECLARE @sql NVARCHAR(MAX);
28DECLARE @ToKill AS NVARCHAR(MAX);
29
30DECLARE @templateSelectTable AS NVARCHAR(MAX) = 'SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName FROM [[#dbname]].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
31DECLARE @templateReindex AS NVARCHAR(MAX) ='
32DECLARE @tryAgain INT = 2;
33WHILE (@tryAgain>0)
34BEGIN
35    BEGIN TRY
36        PRINT ''-- REINDEX Table  : [#tablename]'';
37        ALTER INDEX ALL ON [#tablename] REBUILD WITH (FILLFACTOR = [#fillfactor]));
38    END TRY
39    BEGIN CATCH
40        SET @tryAgain = @tryAgain - 1;
41        WAITFOR DELAY ''00:00:10'';
42        CONTINUE;
43    END CATCH
44    BREAK;
45END;
46'
47SET @templateReindex = REPLACE(@templateReindex,'[#fillfactor])',@fillfactor)
48
49DECLARE @table AS table (TableName nvarchar(256))
50DECLARE @script AS table (Idx int identity,SqlScript nvarchar(max))
51DECLARE @Idx int;
52DECLARE @SqlScript nvarchar(max)
53WHILE (EXISTS(SELECT * FROM #dbs))
54BEGIN
55
56    SELECT TOP 1 @dbname = name FROM #dbs;
57    DELETE FROM #dbs     WHERE name = @dbname;
58
59    SET @sql = REPLACE(@templateSelectTable,'[#dbname]',@dbname);
60    DELETE FROM @table;
61    INSERT INTO @table (TableName)
62    EXEC(@sql)
63
64    INSERT INTO  @script(SqlScript)
65    select REPLACE(@templateReindex,'[#tablename]',TableName) from  @table;
66
67    WHILE (EXISTS(SELECT * FROM @script))
68    BEGIN
69        SELECT TOP 1 @Idx = Idx, @SqlScript = SqlScript FROM @script t;
70        DELETE FROM @script WHERE Idx = @Idx;
71
72        IF (@runScriptRightNow = 1)
73        BEGIN
74            RAISERROR (@SqlScript, 10, 1) WITH NOWAIT;
75            RAISERROR ('GO', 10, 1) WITH NOWAIT;
76            EXEC (@SqlScript);
77        END
78        ELSE
79        PRINT @SqlScript
80    END
81
82END
83GO