Geek Answers Handbook

ALTER INDEX error due to QUOTED_IDENTIFIER when starting from sp_msForEachTable

When I try to rebuild the index in the table:

ALTER INDEX ALL ON [dbo].[Allocations] REBUILD

works fine.

But when I call

 EXECUTE sp_msForEachTable 'ALTER INDEX ALL ON ? REBUILD'

I get the same table and it fails:

Msg 1934, Level 16, State 1, Line 2
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 for computed columns and / or filtered indexes and / or query notifications and / or methods such as XML data and / or spatial index operations.

And to confirm that this is the same table:

 EXECUTE sp_msForEachTable 'print ''Rebuilding ?''; ALTER INDEX ALL ON ? REBUILD; PRINT '' Done ?'''

which gives the results:

 Rebuilding [dbo].[SystemConfiguration] Done [dbo].[SystemConfiguration] Rebuilding [dbo].[UserGroups] Done [dbo].[UserGroups] Rebuilding [dbo].[Groups] Done [dbo].[Groups] Rebuilding [dbo].[UserPermissions] Done [dbo].[UserPermissions] Rebuilding [dbo].[AllocationAdmins] Done [dbo].[AllocationAdmins] Rebuilding [dbo].[Allocations] Msg 1934, Level 16, State 1, Line 2 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.

What am I not doing wrong?

Note

 EXECUTE sp_msForEachTable 'DBCC DBREINDEX(''?'')'

works great!

+7
source share
3 answers

The settings of the quoted identifier are saved for each stored procedure, and sp_MSforeachtable is defined as OFF . However, you can work around this by setting it to ON before it re-indexes:

 create table dbo.T ( ID int not null, constraint PK_T PRIMARY KEY (ID) ) go create view dbo.V ( ID) with schemabinding as select ID from dbo.T go create unique clustered index IX_V on dbo.V(ID) go ALTER INDEX ALL ON dbo.V REBUILD --Fine go exec sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD' --Errors go exec sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD' --Fine

SET QUOTED_IDENTIFIER :

When you create a stored procedure, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS are fixed and used for subsequent calls to this stored procedure.

And, of course, insert the usual sp_MSforeachtable that are undocumented, and therefore you cannot rely on stable behavior.

For DBCC DBREINDEX - all bets are disabled. DBCC lives in its own small, highly tuned world of code. But, of course, one should not rely on future work either:

This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new developments or modify applications that currently use this feature as soon as possible. Use ALTER INDEX instead.

+19
source

You need SET QUOTED_IDENTIFIER ON in sp_msForEachTable because sp_msForEachTable does not have the correct setting.

 EXECUTE sp_msForEachTable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD;'
+3
source

Do not use sp_msforeachtable. It was registered to skip the facility. It will be much better for you to iterate over the list of tables using sys.tables.

 DECLARE @id INT , @table NVARCHAR(256) , @reindex NVARCHAR(4000) SELECT @id = MIN(object_id) FROM sys.tables WHILE @id IS NOT NULL BEGIN SELECT @table = QUOTENAME(s.name) + '.' + QUOTENAME(t.name) FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE t.object_id = @id SELECT @reindex = 'SET QUOTED IDENTIFIER ON; ALTER INDEX ALL ON ' + @table + ' REBUILD;' PRINT @reindex --prints the reindex command --EXEC @reindex --uncomment to actually reindex SELECT @id = MIN(object_id) FROM sys.tables WHERE object_id > @id END

For example:

 CREATE PROCEDURE dbo.sp_ForEachTable @query varchar(8000) AS --todo
+1
source

More articles:


All Articles