有人问我是否可以创建一个存储过程,以将当前数据库中的所有视图复制到另一个视图(通过存储过程参数命名)。
对于上下文,所有数据库都具有相同的架构。这种情况的出现要归功于第三方风险建模工具,该工具可以将每个运行的输出生成为一个全新的数据库(而不是现有数据库中的其他行)。用户希望有一种简便的方法,将20个左右的自定义视图(从“模板”数据库)“按需”应用到另一个相同的数据库。他们希望在一个数据库中维护视图的“最新版本”,然后通过执行此存储过程在其他任何数据库中维护“更新”(拖放)视图。据我所知,这个问题几乎与将视图定义从一个数据库复制到SQL Server中的另一个数据库中的问题完全相同,但从未得到答案。
到目前为止我到达的地方:
获取视图定义:轻松
SELECT @ViewDefinition = definition
FROM sys.sql_modules
WHERE [object_id] = OBJECT_ID('dbo.SampleView');
在SQL Server中将视图定义从一个数据库复制到另一个数据库中的问题甚至包含用于迭代获取所有视图定义的代码。
传递数据库名称作为参数:中
在脚本创建时不知道目标数据库名称很困难。据我所知,这保证了您将依靠Dynamic SQL(EXEC
)来完成所要做的任何事情。
在另一个数据库上创建视图:困难
您不能只添加USE [OtherDatabase]
一些动态CREATE VIEW语句的开头-这会产生错误“ CREATE VIEW must be the first statement in a query batch.
”。而且您也不能只GO
在其中添加一条语句-该错误Incorrect syntax near ‘GO'
提醒您这不是有效的TSQL。一个博客帖子,我发现通过调用解决问题
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”被复制到新数据库。我现在可以扩展这个概念以复制所有视图。但是在我进一步之前...
我错过了这里的记号吗?是否有一个不包含在另一个动态SQL中构建和执行动态SQL的存储过程解决方案?
我认为我找到了一种稍微更好的方法(当我在此例程中增加复杂性时,它将产生更大的变化)。
我最初的实现的主要问题是:
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);
的内部动态执行dbo.sp_executesql @ViewDefinition
需要@ViewDefinition
在动态SQL块内派生视图创建代码。这段代码目前很简单(提取了一个硬编码的视图名称),但是我的计划是对此进行扩展以遍历数据库中的所有视图并将其全部复制。我宁愿不必在动态SQL块中编写此逻辑。
理想情况下,我想让这种逻辑存在于主存储过程空间中,并且只将生成的CREATE VIEW命令传递给动态SQL,但是简单地将CREATE VIEW命令的内容串联起来,这definition
将导致引号转义问题。解决方法是手动对返回的SQL代码进行转义,以便内部动态SQL执行随后可以对其进行“转义”:
DECLARE @ViewDefinition NVARCHAR(MAX);
SELECT @ViewDefinition = definition FROM sys.sql_modules
WHERE [object_id] = OBJECT_ID('dbo.SampleView');
DECLARE @CreateViewStatement NVARCHAR(MAX) = '
DECLARE @SQL NVARCHAR(MAX) = ''' + REPLACE(@ViewDefinition,'''','''''') + '''
EXEC ' + QUOTENAME(@TargetDatabase)+'.dbo.sp_executesql @SQL'
EXEC (@CreateViewStatement);
虽然看起来好像多了一些代码,但主要区别在于,逻辑的内在要在动态SQL之外(仅在针对任意数据库执行该逻辑时才需要)。假设单引号是我在这里唯一需要担心的转义机制,我认为这将使我更加勇往直前,建立遍历所有视图的逻辑,并在需要时设置DROP命令,等等,而不必从偏移量中转义所有内容。如果有完整的解决方案,我会回发。
好了,这是我完整的工作解决方案:
CREATE PROCEDURE [dbo].[usp_Copy_View_To_Database]
@ViewName SYSNAME, -- The name of the view to copy over
@DatabaseName SYSNAME, -- The name of the database to copy the view to
@overwrite bit = 1 -- Whether to overwrite any existing view
AS
IF DB_ID(@DatabaseName) IS NULL -- Validate the database name exists
BEGIN
RAISERROR('Invalid Destination Database Name passed',16,1)
RETURN
END
SET NOCOUNT ON
IF @overwrite = 1 -- If set to overwrite, try to drop the remote view
BEGIN
DECLARE @DropViewStatement NVARCHAR(MAX) =
'EXEC ' + QUOTENAME(@DatabaseName) + '.sys.sp_executesql N''DROP VIEW IF EXISTS ' + QUOTENAME(@ViewName) + ';'';'
EXEC (@DropViewStatement);
END
-- Extract the saved definition of the view
DECLARE @ViewDefinition NVARCHAR(MAX);
SELECT @ViewDefinition = definition FROM sys.sql_modules WHERE [object_id] = OBJECT_ID(@ViewName);
-- Check for a mismatch between the internal view name and the expected name (TODO: Resolve this automatically?)
IF @ViewDefinition NOT LIKE ('%' + @ViewName + '%')
BEGIN
DECLARE @InternalName NVARCHAR(MAX) = SUBSTRING(@ViewDefinition, 3, CHARINDEX(char(10), @ViewDefinition, 3)-4);
PRINT ('Warning: The view named '+@ViewName+' has an internal definition name that is different ('+@InternalName+'). This may have been caused by renaming the view after it was created. You will have to drop and recreate it with the correct name.')
END
-- Substitute any hard-coded references to the current database with the destination database
SET @ViewDefinition = REPLACE(@ViewDefinition, db_name(), @DatabaseName);
-- Generate the dynamic SQL that will create the view on the remote database
DECLARE @CreateViewStatement NVARCHAR(MAX) =
'EXEC ' + QUOTENAME(@DatabaseName) + '.sys.sp_executesql N''' + REPLACE(@ViewDefinition,'''','''''') + ''';'
--PRINT '@CreateViewStatement: ' + @CreateViewStatement -- Can be used for debugging
-- Execute the create statement
EXEC (@CreateViewStatement);
注意:一些转义序列破坏了此处突出显示的语法,它并不像它在动态SQL语句中看起来那样糟糕:我向Highlight.js(当前由StackOverflow使用)提交了一个错误,但事实证明,这实际上是SO的错误禁用某些语言(
tsql
在这种情况下)-因此,如果这也惹恼了您,则也许可以投票赞成此meta.stackexchange帖子:)
该解决方案可以通过以下方式使用:
EXECUTE [dbo].[usp_Copy_View_To_Database] 'SampleView', 'SomeOtherDatabase'
我认为以上内容是存储过程的“适当”工作-复制单个视图。可以通过重复调用此存储的proc来复制多个视图,但是我不一定要在这里自动化它,因为其他人可能有一个他们想要复制的子集,或者使复制顺序变得无关紧要的相互依赖性。
请注意,代码的一部分是对一个相当烦人的问题的警告检查,其中,SQL对于隐藏在幕后定义中的重命名视图可能具有错误的名称。如果我偶然发现了一种优雅的处理方式,则可能会对其进行进一步更新。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句