Alter Index All Tables

參考引用來源
--
SELECT
    RowNum = ROW_NUMBER() OVER(ORDER BY t.TABLE_NAME)
    ,TableName = t.TABLE_SCHEMA + '.' + t.TABLE_NAME
    ,AlterMe = 'ALTER INDEX ALL ON [' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + '] REBUILD;'
INTO #Reindex_Tables
FROM INFORMATION_SCHEMA.TABLES t
WHERE TABLE_TYPE = 'BASE TABLE'


DECLARE @Iter INT
DECLARE @MaxIndex INT
DECLARE @ExecMe VARCHAR(MAX)

SET @Iter = 1
SET @MaxIndex =
(
    SELECT COUNT(1)
    FROM #Reindex_Tables
)

WHILE @Iter < @MaxIndex
BEGIN
    SET @ExecMe =
    (
        SELECT AlterMe
        FROM #Reindex_Tables
        WHERE RowNum = @Iter
    )
 
    EXEC (@ExecMe)
    PRINT @ExecMe + ' Executed'
 
    SET @Iter = @Iter + 1
END


參考引用來源2
select avg_fragmentation_in_percent, avg_fragment_size_in_pages, fragment_count, avg_page_space_used_in_percent
    from sys.dm_db_index_physical_stats (DB_ID(), object_id('[dbo].[YourTableName]'), NULL, NULL, 'DETAILED')

    -- Cursor going over each table and rebuilding every index of database.
    DECLARE @TableName VARCHAR(255)
    DECLARE @sql NVARCHAR(500)
    DECLARE @fillfactor INT
    SET @fillfactor = 80
    DECLARE TableCursor CURSOR FOR
    SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
    FROM sys.tables
    OPEN TableCursor
    FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
    EXEC (@sql)
    FETCH NEXT FROM TableCursor INTO @TableName
    END
    CLOSE TableCursor
    DEALLOCATE TableCursor