Sql-server – How to use MSSQL, rebuild all indexes on all tables? MSSQL Server 2008

fragmentationsql server

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;

Best Answer

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
Related Topic