sql-server - 更改现有约束/规则/程序的 Quoted_Identifier

Question

我目前正在使用 2008-r2 服务器上的旧式数据库，该数据库使用了很多创建时将带引号的标识符设置为关闭的对象。

我主要看这几种:

CHECK_CONSTRAINT
DEFAULT_CONSTRAINT
RULE
SQL_SCALAR_FUNCTION
SQL_STORED_PROCEDURE
SQL_TRIGGER
VIEW

我现在正在尝试更改带引号的标识符设置，当我发现我什至无法更改约束时，这让我很困惑。

对于约束:我想我必须以某种方式制作临时克隆/副本，删除原始文件，然后使用副本和 Quoted_Identifier 设置为 ON 重新创建它们，但我真的不知道该怎么做或者如何自动执行此操作，因为我的 SQL 技能有限。有人可以帮我吗？或者有人知道更简单的替代方法吗？

Answer 1

我在安装过程中有一个错误导致 QUOTED_IDENTIFIER 在大量对象上随机打开/关闭(问题跨越过程、函数、触发器和 View ...)。

由于使用诸如过滤索引和查询通知之类的东西需要 QUOTED_IDENTIFIER ON，所以我想要一种方法来找到所有它关闭的地方并将其打开。

在研究这个网站上的问题时，我发现了这个(以及许多其他)帖子，但我发现没有一个没有重新生成所有 SQL 脚本的好方法(我确实有成千上万的对象需要固定)或编写 C# 代码。

因此我开发了一种基于 SQL 的处理方式。这会重新编译所有的过程，并生成一个列表，其中包含由于某种原因无法编译的任何过程。我知道这不会处理不同的模式(dbo 与销售与其他)，但您可以根据需要对其进行调整。就我而言，我不需要担心这一点。

    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