主页 / server / 问题 / 791878
Accepted
parsecpython
Asked: 2016-07-26 11:57:18 +0800 CST

如何使用 MSSQL，重建所有表上的所有索引？微软 SQL 服务器 2008

  • 772

我有一个 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;
sql-server fragmentation

5 个回答

  1. Best Answer

    您可能可以编写一个使用动态 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
    • 14
    1. 按 Ctrl + T

    2. 运行此查询：

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

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

    • 7

  3. 以@Firdaus 为基础，简单而简单的答案：

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

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

  4. 通常，DBA 会创建一个自动化函数或拥有重建索引的工具。

    这是一个功能版本，用于重建架构中所有表的所有索引：

    DECLARE @tableSchema varchar(max), 
        @tableName varchar(max),
        @tsql nvarchar(max);

DECLARE cur CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME FROM Information_Schema.tables where  table_type ='BASE TABLE'

OPEN cur 

FETCH NEXT FROM cur into @tableSchema, @tableName

WHILE @@FETCH_STATUS = 0 
BEGIN
    SET @tsql ='ALTER INDEX ALL ON [' + @tableSchema + '].[' + @tableName + '] REBUILD;'
    PRINT(@tsql)
    EXEC SP_EXECUTESQL @tsql;
FETCH NEXT FROM cur into @tableSchema, @tableName
END

CLOSE cur
DEALLOCATE cur
    • 1

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

    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;
    • 0

相关问题