Rebuild all indexes in a Database
Try the following script:
Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'GOAlso
I prefer(After a long search) to use the following script, it contains @fillfactor determines how much percentage of the space on each leaf-level page is filled with data.
DECLARE @TableName VARCHAR(255)DECLARE @sql NVARCHAR(500)DECLARE @fillfactor INTSET @fillfactor = 80 DECLARE TableCursor CURSOR FORSELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))+'.' + QUOTENAME(name) AS TableNameFROM sys.tablesOPEN TableCursorFETCH NEXT FROM TableCursor INTO @TableNameWHILE @@FETCH_STATUS = 0BEGINSET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'EXEC (@sql)FETCH NEXT FROM TableCursor INTO @TableNameENDCLOSE TableCursorDEALLOCATE TableCursorGOfor more info, check the following link:
and if you want to Check Index Fragmentation on Indexes in a Database, try the following script:
SELECT dbschemas.[name] as 'Schema',dbtables.[name] as 'Table',dbindexes.[name] as 'Index',indexstats.avg_fragmentation_in_percent,indexstats.page_countFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstatsINNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]AND indexstats.index_id = dbindexes.index_idWHERE indexstats.database_id = DB_ID() AND dbtables.[name] like '%%'ORDER BY indexstats.avg_fragmentation_in_percent descFor more information, Check the following link:
Replace the "YOUR DATABASE NAME" in the query below.
DECLARE @Database NVARCHAR(255) DECLARE @Table NVARCHAR(255) DECLARE @cmd NVARCHAR(1000) DECLARE DatabaseCursor CURSOR READ_ONLY FOR SELECT name FROM master.sys.databases WHERE name IN ('YOUR DATABASE NAME') -- databases AND state = 0 -- database is online AND is_in_standby = 0 -- database is not read only for log shipping ORDER BY 1 OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @Database WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' -- create table cursor EXEC (@cmd) OPEN TableCursor FETCH NEXT FROM TableCursor INTO @Table WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD' PRINT @cmd -- uncomment if you want to see commands EXEC (@cmd) END TRY BEGIN CATCH PRINT '---' PRINT @cmd PRINT ERROR_MESSAGE() PRINT '---' END CATCH FETCH NEXT FROM TableCursor INTO @Table END CLOSE TableCursor DEALLOCATE TableCursor FETCH NEXT FROM DatabaseCursor INTO @Database END CLOSE DatabaseCursor DEALLOCATE DatabaseCursor
Also a good script, although my laptop ran out of memory, but this was on a very large table
https://basitaalishan.com/2014/02/23/rebuild-all-indexes-on-all-tables-in-the-sql-server-database/
USE [<mydatabasename>]Go--/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ---Arguments Data Type Description-------------- ------------ --------------@FillFactor [int] Specifies a percentage that indicates how full the Database Engine should make the leaf level-- of each index page during index creation or alteration. The valid inputs for this parameter-- must be an integer value from 1 to 100 The default is 0.-- For more information, see http://technet.microsoft.com/en-us/library/ms177459.aspx.--@PadIndex [varchar](3) Specifies index padding. The PAD_INDEX option is useful only when FILLFACTOR is specified,-- because PAD_INDEX uses the percentage specified by FILLFACTOR. If the percentage specified-- for FILLFACTOR is not large enough to allow for one row, the Database Engine internally-- overrides the percentage to allow for the minimum. The number of rows on an intermediate-- index page is never less than two, regardless of how low the value of fillfactor. The valid-- inputs for this parameter are ON or OFF. The default is OFF.-- For more information, see http://technet.microsoft.com/en-us/library/ms188783.aspx.--@SortInTempDB [varchar](3) Specifies whether to store temporary sort results in tempdb. The valid inputs for this-- parameter are ON or OFF. The default is OFF.-- For more information, see http://technet.microsoft.com/en-us/library/ms188281.aspx.--@OnlineRebuild [varchar](3) Specifies whether underlying tables and associated indexes are available for queries and data-- modification during the index operation. The valid inputs for this parameter are ON or OFF.-- The default is OFF.-- Note: Online index operations are only available in Enterprise edition of Microsoft-- SQL Server 2005 and above.-- For more information, see http://technet.microsoft.com/en-us/library/ms191261.aspx.--@DataCompression [varchar](4) Specifies the data compression option for the specified index, partition number, or range of-- partitions. The options for this parameter are as follows:-- > NONE - Index or specified partitions are not compressed.-- > ROW - Index or specified partitions are compressed by using row compression.-- > PAGE - Index or specified partitions are compressed by using page compression.-- The default is NONE.-- Note: Data compression feature is only available in Enterprise edition of Microsoft-- SQL Server 2005 and above.-- For more information about compression, see http://technet.microsoft.com/en-us/library/cc280449.aspx.--@MaxDOP [int] Overrides the max degree of parallelism configuration option for the duration of the index-- operation. The valid input for this parameter can be between 0 and 64, but should not exceed-- number of processors available to SQL Server.-- For more information, see http://technet.microsoft.com/en-us/library/ms189094.aspx.--- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*/-- Ensure a USE <databasename> statement has been executed first.SET NOCOUNT ON;DECLARE @Version [numeric] (18, 10) ,@SQLStatementID [int] ,@CurrentTSQLToExecute [nvarchar](max) ,@FillFactor [int] = 100 -- Change if needed ,@PadIndex [varchar](3) = N'OFF' -- Change if needed ,@SortInTempDB [varchar](3) = N'OFF' -- Change if needed ,@OnlineRebuild [varchar](3) = N'OFF' -- Change if needed ,@LOBCompaction [varchar](3) = N'ON' -- Change if needed ,@DataCompression [varchar](4) = N'NONE' -- Change if needed ,@MaxDOP [int] = NULL -- Change if needed ,@IncludeDataCompressionArgument [char](1);IF OBJECT_ID(N'TempDb.dbo.#Work_To_Do') IS NOT NULL DROP TABLE #Work_To_DoCREATE TABLE #Work_To_Do ( [sql_id] [int] IDENTITY(1, 1) PRIMARY KEY , [tsql_text] [varchar](1024) , [completed] [bit] )SET @Version = CAST(LEFT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)), CHARINDEX('.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128))) - 1) + N'.' + REPLACE(RIGHT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)), LEN(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128))) - CHARINDEX('.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)))), N'.', N'') AS [numeric](18, 10))IF @DataCompression IN (N'PAGE', N'ROW', N'NONE') AND ( @Version >= 10.0 AND SERVERPROPERTY(N'EngineEdition') = 3 )BEGIN SET @IncludeDataCompressionArgument = N'Y'ENDIF @IncludeDataCompressionArgument IS NULLBEGIN SET @IncludeDataCompressionArgument = N'N'ENDINSERT INTO #Work_To_Do ([tsql_text], [completed])SELECT 'ALTER INDEX [' + i.[name] + '] ON' + SPACE(1) + QUOTENAME(t2.[TABLE_CATALOG]) + '.' + QUOTENAME(t2.[TABLE_SCHEMA]) + '.' + QUOTENAME(t2.[TABLE_NAME]) + SPACE(1) + 'REBUILD WITH (' + SPACE(1) + + CASE WHEN @PadIndex IS NULL THEN 'PAD_INDEX =' + SPACE(1) + CASE i.[is_padded] WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' END ELSE 'PAD_INDEX =' + SPACE(1) + @PadIndex END + CASE WHEN @FillFactor IS NULL THEN ', FILLFACTOR =' + SPACE(1) + CONVERT([varchar](3), REPLACE(i.[fill_factor], 0, 100)) ELSE ', FILLFACTOR =' + SPACE(1) + CONVERT([varchar](3), @FillFactor) END + CASE WHEN @SortInTempDB IS NULL THEN '' ELSE ', SORT_IN_TEMPDB =' + SPACE(1) + @SortInTempDB END + CASE WHEN @OnlineRebuild IS NULL THEN '' ELSE ', ONLINE =' + SPACE(1) + @OnlineRebuild END + ', STATISTICS_NORECOMPUTE =' + SPACE(1) + CASE st.[no_recompute] WHEN 0 THEN 'OFF' WHEN 1 THEN 'ON' END + ', ALLOW_ROW_LOCKS =' + SPACE(1) + CASE i.[allow_row_locks] WHEN 0 THEN 'OFF' WHEN 1 THEN 'ON' END + ', ALLOW_PAGE_LOCKS =' + SPACE(1) + CASE i.[allow_page_locks] WHEN 0 THEN 'OFF' WHEN 1 THEN 'ON' END + CASE WHEN @IncludeDataCompressionArgument = N'Y' THEN CASE WHEN @DataCompression IS NULL THEN '' ELSE ', DATA_COMPRESSION =' + SPACE(1) + @DataCompression END ELSE '' END + CASE WHEN @MaxDop IS NULL THEN '' ELSE ', MAXDOP =' + SPACE(1) + CONVERT([varchar](2), @MaxDOP) END + SPACE(1) + ')' ,0FROM [sys].[tables] t1INNER JOIN [sys].[indexes] i ON t1.[object_id] = i.[object_id] AND i.[index_id] > 0 AND i.[type] IN (1, 2)INNER JOIN [INFORMATION_SCHEMA].[TABLES] t2 ON t1.[name] = t2.[TABLE_NAME] AND t2.[TABLE_TYPE] = 'BASE TABLE'INNER JOIN [sys].[stats] AS st WITH (NOLOCK) ON st.[object_id] = t1.[object_id] AND st.[name] = i.[name]SELECT @SQLStatementID = MIN([sql_id])FROM #Work_To_DoWHERE [completed] = 0WHILE @SQLStatementID IS NOT NULLBEGIN SELECT @CurrentTSQLToExecute = [tsql_text] FROM #Work_To_Do WHERE [sql_id] = @SQLStatementID PRINT @CurrentTSQLToExecute EXEC [sys].[sp_executesql] @CurrentTSQLToExecute UPDATE #Work_To_Do SET [completed] = 1 WHERE [sql_id] = @SQLStatementID SELECT @SQLStatementID = MIN([sql_id]) FROM #Work_To_Do WHERE [completed] = 0END