Changing Quoted_Identifier on existing Constraints / Rules / Procedures

Question

I am currently working with an old-ish database on a 2008-r2 Server which uses a lot of objects that were created with Quoted Identifier set to off.

I am mainly looking at these types:

CHECK_CONSTRAINT
DEFAULT_CONSTRAINT
RULE
SQL_SCALAR_FUNCTION
SQL_STORED_PROCEDURE
SQL_TRIGGER
VIEW

I am now trying to change the Quoted Identifier Setting which had me stumped right away as I discovered that I can't even alter a Constraint.

For the constraints: I am thinking that I have to somehow make a temporary clone/copy, delete the original and then recreate them using the copy and the Quoted_Identifier set to ON, but I don't really know how to do this or how to automate this as my SQL-skills are limited. Could someone help me? Or does somebody know of an easier alternative methode?

Answer 1

I had a bug in an installation process which caused QUOTED_IDENTIFIER to be randomly ON/OFF over a very large number of objects (the problem spanned procs, functions, triggers, and views...).

Since using things like filtered indexes and query notifications require QUOTED_IDENTIFIER ON, I wanted a way to find all of the ones where it was OFF and turn it ON.

In researching the issue on this site, I found this (and a number of other) posts, but none I found had a good way to do it without regenerating all the SQL scripts (I literally have thousands of objects that need to be fixed) or writing C# code.

So I developed a SQL-based way of handling it. This recompiles all the procs, and produces a list of any that for some reason it was unable to compile. I'm aware that this doesn't handle varying schemas (dbo vs. sales vs. whatever), but you could adapt it as necessary for that. I didn't need to worry about that in my case.

    SET NOCOUNT ON

    -- MAKE SURE THIS IS ON!!
    SET QUOTED_IDENTIFIER ON

    --- Used in try/catch below
    DECLARE @ErrorMessage nvarchar(4000);
    DECLARE @ErrorSeverity int;
    DECLARE @ErrorState int;

    DECLARE @name sysname
    DECLARE @type char(2)
    DECLARE @objType nvarchar(50)
    DECLARE @createCommand nvarchar(max)
    DECLARE @dropCommand nvarchar(max)
    DECLARE @success bit

    IF OBJECT_ID(N'tempdb..#ProcList', N'U') IS NOT NULL DROP TABLE #ProcList

    CREATE TABLE #ProcList
    (
        name            sysname         NOT NULL PRIMARY KEY,
        id              int             NOT NULL,
        type            char(2)         NOT NULL,
        definition      nvarchar(max)   NULL,
        alterstmt       nvarchar(max)   NULL,
        processed       bit             NOT NULL,
        successful      bit             NOT NULL
    )

    --- Build the list of objects that have quoted_identifier off
    INSERT INTO #ProcList
    SELECT 
        so.name, 
        so.object_id, 
        so.type, 
        sm.definition, 
        NULL, 
        0, 
        0
    FROM sys.objects so
        INNER JOIN sys.sql_modules sm
            ON so.object_id = sm.object_id
    WHERE 
        LEFT(so.name, 3) NOT IN ('sp_', 'xp_', 'ms_')
        AND sm.uses_quoted_identifier = 0
    ORDER BY 
        name

    -- Get the first object
    SELECT @name = MIN(name) FROM #ProcList WHERE processed = 0

    --- As long as we have one, keep going
    WHILE (@name IS NOT NULL)
    BEGIN

        SELECT
            @createCommand = definition,
            @type = type
        FROM #ProcList 
        WHERE name = @name

        --- Determine what type of object it is
        SET @objType = CASE @type
                WHEN 'P'  THEN 'PROCEDURE' 
                WHEN 'TF' THEN 'FUNCTION'
                WHEN 'IF' THEN 'FUNCTION'
                WHEN 'FN' THEN 'FUNCTION'
                WHEN 'V'  THEN 'VIEW'
                WHEN 'TR' THEN 'TRIGGER'
            END

        --- Create the drop command
        SET @dropCommand = 'DROP ' + @objType + ' ' + @name

        --- record the drop statement that we are going to execute
        UPDATE #ProcList 
        SET 
            processed = 1, 
            alterstmt = @dropCommand 
        WHERE name = @name

        --- Assume we will not succeed
        SET @success = 0

        BEGIN TRANSACTION

        --- Drop the current proc
        EXEC sp_executesql @dropCommand

        BEGIN TRY

            --- Execute the create statement from the definition
            EXEC sp_executesql @createCommand

            --- If we reached this point, it all worked
            SET @success = 1

            COMMIT

        END TRY
        BEGIN CATCH

            --- oops something went wrong
            SELECT
                @ErrorMessage = ERROR_MESSAGE(),
                @ErrorSeverity = ERROR_SEVERITY(),
                @ErrorState = ERROR_STATE();

            PRINT 'Error processing ' + @name
            RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @name)

            --- Undo the transaction, which undoes the drop above
            ROLLBACK

        END CATCH

        --- At this point, there should be no open transactions
        IF @@TRANCOUNT > 0 
        BEGIN
            PRINT 'ERROR... transaction count not right!!'
            ROLLBACK
            RETURN
        END

        --- check to make sure the object still exists after executing the alter statement, and that we didn't detect an earlier error
        --- If it's all good, then mark the proc as having been successful
        IF (
            @success = 1
            AND EXISTS (
                SELECT name 
                FROM sys.objects so 
                    INNER JOIN sys.sql_modules sm 
                        ON so.object_id = sm.object_id 
                WHERE name = @name
            )
        )
        BEGIN
            UPDATE #ProcList SET successful = 1 WHERE name = @name
        END      

        -- Get the next one... if none are left the result will be NULL
        SELECT @name = MIN(name) FROM #ProcList where processed = 0

    END

    -- What wasn't successful??
    SELECT * 
    FROM #ProcList 
    WHERE successful = 0 
    ORDER BY name