How to use MSSQL, rebuild all indexes on all tables? MSSQL Server 2008
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
- 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.
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'
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