How to ALTER INDEX for every Table in my DataBase

Question

I use SQL Server 2008 R2, I need to rebuild the index for every table in a database

Using this script, I receive an error

USE myDb
GO

EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD'

Error:

ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Any idea how to fix it? thanks

Answer 1

SQL Fool (Michelle Ufford) has a great script to do this for you - all done and well tested by many users.

It's a great piece of work - it allows you to define fragmentation levels for which you

• do nothing
• reorganize an index
• rebuild an index

Don't reinvent the wheel - just go see and use the script!

Answer 2

A simple approch I decide to use for my questions. Code from: http://blog.sqlauthority.com/2009/01/30/sql-server-2008-2005-rebuild-every-index-of-all-tables-of-database-rebuild-index-with-fillfactor/

   -- Rebuild eve Index for every Table in the Database.
    -- Resource: http://blog.sqlauthority.com/2009/01/30/sql-server-2008-2005-rebuild-every-index-of-all-tables-of-database-rebuild-index-with-fillfactor/
    USE [YourDbName]
    GO

    -- Show Fragmentation sample on YourTable Index.
    select avg_fragmentation_in_percent, avg_fragment_size_in_pages, fragment_count, avg_page_space_used_in_percent
    from sys.dm_db_index_physical_stats (DB_ID(), object_id('[dbo].[YourTableName]'), NULL, NULL, 'DETAILED')

    -- Cursor going over each table and rebuilding every index of database.
    DECLARE @TableName VARCHAR(255)
    DECLARE @sql NVARCHAR(500)
    DECLARE @fillfactor INT
    SET @fillfactor = 80
    DECLARE TableCursor CURSOR FOR
    SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
    FROM sys.tables
    OPEN TableCursor
    FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
    EXEC (@sql)
    FETCH NEXT FROM TableCursor INTO @TableName
    END
    CLOSE TableCursor
    DEALLOCATE TableCursor
    GO