Como usar o MSSQL, reconstruir todos os índices em todas as tabelas? MSSQL Server 2008

Question

Eu tenho um banco de dados mssql, vamos chamá-lo: mssqlDB01. Eu fui encarregado de executar a desfragmentação em todas as tabelas. Este banco de dados tem algumas centenas de tabelas e cada tabela possui um intervalo de 1 a 15 índices por tabela.

O Google me levou a descobrir uma prática para desfragmentar todos os índices por tabela, mas não consigo descobrir como fazer isso em todas as tabelas.

ALTER INDEX ALL ON TABLENAME REBUILD;

o que estou procurando é

ALTER INDEX ALL ON * REBUILD; 

mas reclama

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

abaixo me permite encontrar todas as tabelas no meu banco de dados

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

posso de alguma forma colocar isso no comando?

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

    

Answer 1

Você provavelmente poderia escrever um script que usa SQL dinâmico para fazer isso, mas por que fazer isso quando você pode usar outra pessoa? Ola Hallengren é o mais conhecido e gratuito, mas Minion Ware também tem um script de reindexação gratuito .

Se você insistir em escrever por conta própria, algo assim pode funcionar:

Use mssqlDB01

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


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

while @TBname is not null

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

    

Answer 2

Isso reconstruirá os índices e manterá as configurações de compactação se você tiver alguma:

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;