How to make ALTER INDEX for every table in my database

Question

I am using SQL Server 2008 R2, I need to rebuild the index for each table in the database

Using this script, I get an error

USE myDb
GO

EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD'

Error:

ALTER INDEX error because the following SET parameters have incorrect settings: "QUOTED_IDENTIFIER". Ensure that the SET parameters 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 of type and / or spatial index operation.

Any idea how to fix this? thank

Answer 1

SQL Fool (Michelle Ufford) script, - .

- ​​ ,

- script!

Answer 2

. : 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