How to use MSSQL, rebuild all indexes on all tables? MSSQL Server 2008

Question

I have a mssql database, we'll call it: mssqlDB01. I've been tasked with performing defragmentation on all of the tables. This database has a few hundred tables and each table has a range of 1 to 15 indexes per table.

Google led me to discover a practice to defrag all indexes per table, but I can't figure out how to do it on all tables.

ALTER INDEX ALL ON TABLENAME REBUILD;

what i'm looking for is

ALTER INDEX ALL ON * REBUILD;

but it complains

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

below lets me find all tables in my DB

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

can I somehow push this into the command?

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

`EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"` — StingyJack, Jan 21 '19 at 11:48

score 14 · Accepted Answer · edited Jun 05 '20 at 12:27

You could probably write a script that uses dynamic SQL to do that, but why do that when you can use someone else's? Ola Hallengren's are the best known and free, but Minion Ware also has a free reindex script.

If you insist on writing it yourself, something like this might work:

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

score 7 · Answer 2 · edited Jan 22 '19 at 10:11

7

Press Ctrl + T

Run this query:

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

Copy the output and paste it into the SQL window, then click on run.

edited Jan 22 '19 at 10:11

Lacek

6,585
22
28

answered Jan 21 '19 at 11:27

Firdaus

71
1
1

It would be good to add "[" and "]" before and after the table (I can't add it as a change need to have 6 chars) – Philipp Aug 14 '20 at 21:13

score 2 · Answer 3 · answered Jan 16 '20 at 15:10

Building on @Firdaus nice and simple answer:

If your database has schemas, try running the following in SSMS:

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

score 1 · Answer 4 · answered Jul 25 '20 at 22:24

Normally DBAs create an automated function or have tools to rebuild the indexes.

This one is a functional version to rebuild all indexes to all tables in your schema:

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

score 0 · Answer 5 · answered Feb 13 '18 at 15:05

This will rebuild the indexes and keep the compression settings if you have any:

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;

How to use MSSQL, rebuild all indexes on all tables? MSSQL Server 2008

5 Answers5