☰

如何使用 MSSQL，重建所有表上的所有索引？MSSQL Server 2008

tag-icon • tag-icon sql-server fragmentation
如何使用 MSSQL，重建所有表上的所有索引？MSSQL Server 2008

我有一个 mssql 数据库，我们将其称为：mssqlDB01。我负责对所有表执行碎片整理。该数据库有几百个表，每个表都有 1 到 15 个索引。


谷歌让我发现了一种对每个表的所有索引进行碎片整理的做法，但我不知道如何在所有表上执行此操作。

ALTER INDEX ALL ON TABLENAME REBUILD;

我正在寻找的是

ALTER INDEX ALL ON * REBUILD;

但它抱怨

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '*'.`

下面让我找到我的数据库中的所有表

SELECT * FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE'

我可以以某种方式将其推入命令吗？

ALTER INDEX ALL ON (SELECT * FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE'; ) REBUILD;

答案1

您可能可以编写一个使用动态 SQL 的脚本来执行此操作，但如果可以使用其他人的脚本，为什么要这样做呢？ Ola Hallengren 的是最知名且免费的，但是Minion Ware 还有一个免费的重新索引脚本。

如果你坚持自己写，那么类似这样的方法可能会有效：

Use mssqlDB01

Declare @TBname nvarchar(255),
        @schema nvarchar(255),
        @SQL nvarchar(max) 


select @TBname = min(TABLE_NAME) from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
select @schema = SCHEMA_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' and TABLE_NAME = @TBname

while @TBname is not null
BEGIN
    set @SQL='ALTER INDEX ALL ON [' + @schema + '].[' + @TBname + '] REBUILD;'
    --print @SQL
    EXEC SP_EXECUTESQL @SQL
    select @TBname = min(TABLE_NAME) from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
    select @schema = SCHEMA_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' and TABLE_NAME = @TBname      
END

答案2

  1. 按 Ctrl + T

  2. 运行此查询：

    SELECT 'ALTER INDEX ALL ON ' + table_name + '  REBUILD;' 
    FROM Information_Schema.tables where  table_type ='BASE TABLE'

  3. 复制输出并将其粘贴到 SQL 窗口，然后单击运行。

答案3

基于@Firdaus 的简单明了的回答：

如果您的数据库具有模式，请尝试在 SSMS 中运行以下命令：

SELECT 'ALTER INDEX ALL ON ' + TABLE_SCHEMA + '.' + table_name + '  REBUILD;' 
    FROM Information_Schema.tables where  table_type ='BASE TABLE'

答案4

这将重建索引并保留压缩设置（如果有）：

DECLARE
    @schemaName sysname,
    @tableName sysname,
    @compressionType VARCHAR(50),
    @sql NVARCHAR(1000)

DECLARE table_cursor CURSOR FAST_FORWARD
FOR
SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    p.data_compression_desc AS CompressionType
FROM
    sys.partitions AS p
    INNER JOIN sys.tables AS t ON t.object_id = p.object_id
WHERE
    p.index_id IN (0, 1)

OPEN table_cursor

FETCH NEXT FROM table_cursor
INTO @schemaName, @tableName, @compressionType

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'ALTER INDEX ALL ON [' + @schemaName + '].[' + @tableName + '] REBUILD'
        + CASE WHEN @compressionType <> 'NONE' 
            THEN ' PARTITION = ALL WITH(DATA_COMPRESSION = ' + @compressionType + ')'
            ELSE ''
          END

    PRINT @sql
    EXEC sys.sp_executesql @SQL

    FETCH NEXT FROM table_cursor   
    INTO @schemaName, @tableName, @compressionType
END

CLOSE table_cursor;  
DEALLOCATE table_cursor;

相关内容