如何批量为已安装的数百个存储过程开启Quoted Identifiers

问题场景 #

将数百个存储过程部署到数据库时，QUOTED_IDENTIFIER 被设置为关闭状态，需要统一开启该配置。
可通过以下查询定位所有配置不符合要求的存储过程：

SELECT 
  name = OBJECT_NAME([object_id]), 
  uses_quoted_identifier
FROM sys.sql_modules
WHERE uses_quoted_identifier <> 1 
  AND OBJECT_NAME([object_id]) LIKE '%%'
ORDER BY OBJECT_NAME([object_id])

已知操作限制 #

• 无法直接通过更新系统视图 sys.sql_modules 修改 uses_quoted_identifier 属性
• 手动逐份打开存储过程脚本重新安装的流程耗时极长
• 即便编写基础批处理执行重装，效率提升也非常有限，仅能小幅减少重复操作量

已验证的即时解决方案 #

• 根因定位：此前使用批处理从指定目录批量部署存储过程时，未在执行命令中配置QUOTED_IDENTIFIER相关参数，导致所有部署的存储过程默认带上了QUOTED_IDENTIFIER OFF的元数据标记
• 部署流程修正：在调用sqlcmd执行脚本部署时，添加-I参数即可在会话级别启用QUOTED_IDENTIFIER，后续新部署的存储过程会自动带上正确的配置

无需依赖原始脚本的批量修正技巧 #

如果没有留存所有存储过程的原始创建脚本，可直接基于系统存储的模块定义完成批量修正，操作步骤如下：

1. 在当前执行会话中预先开启正确的配置，确保后续重编译操作带上正确的元数据标记：

   SET QUOTED_IDENTIFIER ON;
   SET ANSI_NULLS ON;
   

2. 运行以下查询，自动生成针对所有配置异常存储过程的重编译命令：

   SELECT 
     'EXEC sp_refreshsqlmodule ''' + SCHEMA_NAME(p.schema_id) + '.' + p.name + ''';'
   FROM sys.procedures p
   INNER JOIN sys.sql_modules m 
     ON p.object_id = m.object_id
   WHERE m.uses_quoted_identifier <> 1
     AND m.is_ms_shipped = 0;
   

   注意：执行生成的重编译命令时，必须保持当前会话的上述配置不变，否则重编译后存储过程的元数据配置会和当前会话设置保持一致，无法完成修正。

3. 批量执行所有生成的重编译命令后，重新运行最开始的定位查询，验证所有存储过程的uses_quoted_identifier是否已全部修正为1。

内容的提问来源于stack exchange，提问作者jprice92