SQLSERVER：数据库维护任务（索引重建、统计信息更新等）

-- 重建特定表的所有索引
ALTER INDEX ALL ON [YourDatabaseName].[dbo].[YourTableName] REBUILD;


-- 重建特定索引
ALTER INDEX [YourIndexName] ON [YourDatabaseName].[dbo].[YourTableName] REBUILD;


ALTER INDEX ALL ON [sales].[dbo].[x_user] REBUILD;

-- 重组特定表的所有索引
ALTER INDEX ALL ON [YourDatabaseName].[dbo].[YourTableName] REORGANIZE;


-- 重组特定索引
ALTER INDEX [YourIndexName] ON [YourDatabaseName].[dbo].[YourTableName] REORGANIZE;

-- 更新特定表的所有统计信息
UPDATE STATISTICS [YourDatabaseName].[dbo].[YourTableName];


-- 更新特定统计信息
UPDATE STATISTICS [YourDatabaseName].[dbo].[YourTableName] [YourStatisticName];

-- 创建 SQL Server Agent 作业来重建索引
-- 注意：以下脚本需要在 SQL Server Management Studio (SSMS) 的作业步骤中编写


BEGIN
    -- 重建数据库中所有表的所有索引
    DECLARE @TableName VARCHAR(255)
    DECLARE TableCursor CURSOR FOR
    SELECT [name]
    FROM sys.tables


    OPEN TableCursor
    FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC('ALTER INDEX ALL ON [' + @TableName + '] REBUILD')
        FETCH NEXT FROM TableCursor INTO @TableName
    END


    CLOSE TableCursor
    DEALLOCATE TableCursor
END

-- 创建 SQL Server Agent 作业来更新统计信息
-- 注意：以下脚本需要在 SQL Server Management Studio (SSMS) 的作业步骤中编写


BEGIN
    -- 更新数据库中所有表的统计信息
    DECLARE @TableName VARCHAR(255)
    DECLARE TableCursor CURSOR FOR
    SELECT [name]
    FROM sys.tables


    OPEN TableCursor
    FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC('UPDATE STATISTICS [' + @TableName + ']')
        FETCH NEXT FROM TableCursor INTO @TableName
    END


    CLOSE TableCursor
    DEALLOCATE TableCursor
END

-- 检查索引碎片化
SELECT 
    dbschemas.[name] as 'Schema', 
    dbtables.[name] as 'Table', 
    dbindexes.[name] as 'Index',
    indexstats.avg_fragmentation_in_percent
FROM 
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER 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_id
WHERE 
    indexstats.database_id = DB_ID()
    AND indexstats.avg_fragmentation_in_percent > 5 -- 只显示碎片化超过5%的索引
ORDER BY 
    indexstats.avg_fragmentation_in_percent DESC;