Code Example: Enabling Quoted Identifier in SQL Using SET

Feedback


Solution 1:

The stored procedure

sp_MSforeachtable

has its quoted identifier settings defined as

OFF

. However, you can bypass this by setting it to

ON

before executing the re-index.

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

:

Upon the creation of a stored procedure, the captured settings for

SET QUOTED_IDENTIFIER

and

SET ANSI_NULLS

are utilized in subsequent invocations of the stored procedure.


Furthermore, it is important to mention the typical disclaimers regarding the fact that

sp_MSforeachtable

lacks documentation, rendering it unreliable and unpredictable in terms of its stability.


Regarding

DBCC DBREINDEX

, all expectations are nullified. Each

DBCC

operates within a distinctive, highly personalized coding realm. However, it is imperative to not depend on it for future endeavors.

In an upcoming version of Microsoft SQL Server, this feature will no longer be available. It is advised not to utilize this feature in new development projects and to promptly update applications that currently rely on it. Instead, employ

ALTER INDEX

.


Solution 2:


In both the

SET QUOTED_IDENTIFIER ON

and

sp_msForEachTable

, it is necessary to have the appropriate settings, as

sp_msForEachTable

lacks the correct configuration.

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


Solution 3:


Avoid using sp_msforeachtable, as it has been found to overlook objects. Instead, it is recommended to iterate through a list of tables using sys.tables for better results.

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

Frequently Asked Questions