sql - 将 View 从当前数据库复制到另一个数据库的存储过程

Question

有人问我是否可以创建一个存储过程，将当前数据库中的所有 View 复制到另一个 View (通过存储过程参数命名)。
对于上下文，所有数据库都具有相同的模式。这种情况的出现要归功于 3rd 方风险建模工具，该工具将每次运行的输出生成为一个全新的数据库(而不是现有数据库中的附加行)。用户想要一种简单的方法来按需将他们的 20 个左右的自定义 View (来自他们的"template"数据库)“应用”到另一个相同的数据库。他们希望在一个数据库中维护 View 的“最新版本”，然后通过执行此存储过程在任何其他数据库上“更新”(删除 + 创建) View 。据我所知，这个问题与 Copy a view definition from one database to another one in SQL Server 中的问题几乎相同。 ，从未得到答复。

到目前为止我得到的地方:

获取 View 定义:简单

SELECT @ViewDefinition = definition
FROM sys.sql_modules
WHERE [object_id] = OBJECT_ID('dbo.SampleView');

问题在 Copy a view definition from one database to another one in SQL Server甚至有用于迭代获取所有 View 定义的代码。

将数据库名称作为参数传递:中
在脚本创建时不知道目标数据库名称是很困难的。据我所知，这保证您将依赖动态 SQL ( EXEC ) 来做您正在做的任何事情。

在另一个数据库上创建 View :困难
您不能只添加 USE [OtherDatabase]到一些动态 CREATE VIEW 语句的开头 - 这会产生错误“CREATE VIEW must be the first statement in a query batch.”。而且您不能只添加 GO在那里的声明 - 错误 Incorrect syntax near ‘GO'提醒您这不是有效的 TSQL。 blog post我发现通过调用解决了这个问题

EXEC [SomeOtherDatabase].dbo.sp_executesql @CreateViewSQL

但不幸的是，此解决方案不能用于将“SomeOtherDatabase”作为参数传入的上下文中。

这让我陷入了不得不 的令人难以置信的尴尬境地。从另一个动态 SQL 语句中构造和执行动态 SQL 语句 .

所以目前我的概念验证解决方案如下所示:

ALTER PROCEDURE [dbo].[usp_Enhance_Database_With_Views]
    @TargetDatabase SYSNAME,
AS
    IF DB_ID(@TargetDatabase) IS NULL  /*Validate the database name exists*/
    BEGIN
       RAISERROR('Invalid Database Name passed',16,1)
       RETURN
    END
    
    DECLARE @CreateViewStatement NVARCHAR(MAX) = '
        DECLARE @ViewDefinition NVARCHAR(MAX);
        SELECT @ViewDefinition = definition FROM sys.sql_modules 
        WHERE [object_id] = OBJECT_ID(''dbo.SampleView'');
        EXEC ' + QUOTENAME(@TargetDatabase) + '.dbo.sp_executesql @ViewDefinition'
    EXEC (@CreateViewStatement);

我在网上找不到其他类似的东西，但令人惊讶的是(对我来说)它有效。 “SampleView”被复制到新数据库。我现在可以扩展这个概念以复制所有 View 。但在我走得更远之前......

我错过了这里的标记吗？是否有不包括在另一个动态 SQL 中构建和执行动态 SQL 的存储过程解决方案？

Answer 1

Have I missed the mark here? Is there a stored procedure solution that doesn't include building and executing dynamic SQL within another dynamic SQL?

不。如果你想用 TSQL 做到这一点，这就是方法。