sql-server - 使用动态 Sql 创建 View

Question

我正在尝试创建动态数据库创建脚本。

有很多步骤，而且我们经常创建这个数据库，因此脚本看起来像这样。

 DECLARE @databaseName nvarchar(100) = 'DatabaseName'
 EXEC('/*A lot of database creation code built off of @databaseName*/')

这一切都很好，除了我们想在 @databaseName 中创建的一个 View 。

我尝试了四种不同的方法来创建此 View ，但没有成功:

1. 我的第一个想法是简单地设置数据库上下文，然后在一个脚本中创建 View 。不幸的是，这不起作用，因为 CREATE VIEW 必须是其查询 block ( details ) 中的第一个语句。

   --Result: Error message, "'CREATE VIEW' must be the first statement in a query batch"
   EXEC 
   ('
       USE [' + @databaseName + ']
       CREATE VIEW
   ')
   

2. 为了解决 (1)，我尝试单独设置上下文，以便 CREATE VIEW 成为 EXEC 中的第一个命令。这确实创建了 View ，但是在我当前的上下文中而不是 @databaseName 中创建的。看来，在 EXEC 中调用 USE 的效果只会持续到 EXEC 语句结束 ( details )。

   --Result: The view is created in the currently active database rather than @databaseName
   EXEC ('USE [' + @databaseName + ']')
   EXEC ('CREATE VIEW')
   

3. 接下来，我尝试将所有内容放回一个脚本中，但包含一个 GO 命令，以使 CREATE VIEW 成为新查询 block 中的第一个命令。此操作失败，因为 EXEC 脚本 ( details ) 中不允许使用 GO。

   --Result: Error message, "Incorrect syntax near 'GO'"
   EXEC 
   ('
       USE [' + @databaseName + ']
       GO
       CREATE VIEW
   ')
   

4. 最后，我尝试将目标数据库指定为 CREATE VIEW 命令的一部分。在本例中，脚本失败，因为 CREATE VIEW 不允许在其创建过程中指定数据库 ( details )。

   --Result: Error message, "'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name"
   EXEC ('CREATE VIEW [' + @databaseName + '].[dbo].[ViewName]')
   

有什么建议吗？我认为这应该是一个常见的用例，但 Google 无法帮助我。

Answer 1

您可以通过双重嵌套动态 SQL 语句来完成此操作:

begin tran
declare @sql nvarchar(max) = 
    N'use [AdventureWorks2012]; 
      exec (''create view Test as select * from sys.databases'')';

exec (@sql);

select * from AdventureWorks2012.sys.views
where name = 'Test'

rollback tran