主页 / dba / 问题 / 322580
Accepted
Thomas Franz
Asked: 2023-01-26 09:42:47 +0800 CST

MS SQL Server：在其他数据库中动态创建过程/函数/视图/触发器（sp_executesql）

  • 772

问题：

  1. 您想要/需要使用动态 SQL 在当前数据库之外的其他数据库中创建过程/函数/视图/触发器
  2. 你不能在语句中指定数据库名称，CREATE VIEW tempdb.dbo.v_test AS SELECT 1; 将失败并显示错误 166：“CREATE/ALTER VIEW”不允许将数据库名称指定为对象名称的前缀。
  3. ( CREATEor ALTER) 语句必须是批处理中的第一个语句，否则您将收到错误 111：“CREATE ...”必须是查询批处理中的第一个语句。. 出于这个原因，你不能简单地写USE <database>在CREATE：
DECLARE @create_sql NVARCHAR(max);
SET @create_sql = CONCAT('USE tempdb', CHAR(13) + CHAR(10),
                         'CREATE VIEW dbo.v_test AS SELECT 1 AS n;'
                        );
EXEC (@create_sql) -- fails
-- or
EXEC sys.sp_executesql @create_sql -- fails too
  1. 你不能GO在EXEC()orEXEC sys.sp_executesql
    语句中使用，因为它不在 SQL 标准中（只是在 SSMS 和其他几个 SQL 工具中使用的可配置虚拟词），所以你不能简单地在 USE 和 CREATE 命令之间添加它上面的脚本就像您在创建“正常”部署脚本时在 SSMS 中执行的那样
  2. 内部过程sys.sp_executesql存在于每个数据库中，并在其自己的数据库中执行给定的命令，但是可能有很多（例如一百个）数据库，因此您不能真正使用多个IF语句在要执行它的地方“硬编码”
sql-server

1 个回答

  1. Best Answer

    解决方案：

    您必须使用三次嵌套调用。

    1. 您sys.sp_executesql在当前数据库中调用（例如master）
    2. sys.sp_executesql这在目标数据库中调用（例如 tempdb）
    3. 这最终调用 EXEC (@create_sql) 来创建过程/触发器/函数/触发器等。

    由于该CREATE语句是作为变量而不是字符串传递的，因此您不必担心引号、特殊字符或 sql 注入——当然除了初始的CREATE（示例中的@create_sql）

    例子：

    USE master;
DECLARE @dest_db_name sysname       = 'tempdb'
      , @create_sql   NVARCHAR(max) = 'CREATE VIEW dbo.v_test AS SELECT 1 AS n;'
      , @dummy_cmd    NVARCHAR(500)
;
                                                             -- second exec        -- third exec
SET @dummy_cmd =  CONCAT('EXEC ', QUOTENAME(@dest_db_name), '.sys.sp_executesql N''EXEC (@create_sql)'', N''@create_sql NVARCHAR(MAX)'', @create_sql = @create_sql;')
     -- first exec 
EXEC sys.sp_executesql @dummy_cmd, N'@create_sql NVARCHAR(MAX)', @create_sql = @create_sql

-- control
SELECT *  FROM tempdb.dbo.v_test

-- DROP can be used with other statements in the same batch, so it is much easier and doesn't need special treatment
SET @dummy_cmd = 'USE tempdb;' + CHAR(13) + CHAR(10) + 'DROP VIEW IF EXISTS dbo.v_test;'
EXEC (@dummy_cmd)
    • 1

相关问题