I took an old script I had and modified it to:
- Include Schema support.
- Replace the old DBCC DBREINDEX statement with ALTER INDEX ALL.
- Rebuild the indexes online if the Enterprise edition was installed.
- Generate information about the tables processed.
Here is the script:
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE @Options varchar(50)
-- detecting if enterprise edition is installed to rebuild the indexes online
SELECT @Options =
CASE
WHEN LOWER(CONVERT(VARCHAR, SERVERPROPERTY('Edition'))) LIKE '%enterprise%'
THEN ' WITH(ONLINE = ON)'
ELSE ''
END
DECLARE TableCursor CURSOR FOR
SELECT '['+table_schema+'].['+table_name+']' FROM information_schema.tables
WHERE lower(table_type) = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Rebuilding all indexes on table '+@TableName+'...'
BEGIN TRY
EXEC('ALTER INDEX ALL ON '+@TableName+' REBUILD'+@Options)
PRINT 'Done.'
END TRY
BEGIN CATCH
PRINT 'Failed.'
END CATCH;
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
The original script can be found here:
http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx
This article can also be useful:
http://blogs.techrepublic.com.com/datacenter/?p=249