Jan 30

Written by: Javier Callico
1/30/2009 

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