将视图定义从一个数据库复制到SQL Server中的另一个数据库 [英] Copy a view definition from one database to another one in SQL Server

问题描述

我正在开发一个脚本，该脚本允许我创建另一个数据库( database2 )中 database1 中存在的所有视图.

I am developping a script that allows me to create all the views present in database1 in another database (database2).

我正在尝试使用在 database1 的所有视图上循环的游标执行此操作，然后尝试在第二个数据库上执行该视图的定义.

I am trying to do that using a cursor that loop on all the views of database1 and then try to execute the definition of that view on the second database.

不幸的是，它不起作用.我收到以下错误:

Unfortunately it doesn't work. I get the following error:

语法不正确，请转到"".
消息111，妮维雅15，État1，利涅14
'CREATE VIEW'要求性状指导书.

Syntaxe incorrecte vers 'go'.
Msg 111, Niveau 15, État 1, Ligne 14
'CREATE VIEW' doit être la première instruction d'un traitement de requêtes.

这是我的代码

declare @database2 varchar(50), @database1 varchar(50)
set @database2 = 'Local'
set @database1 = 'prod'

declare @Query nvarchar(max), @view_definition nvarchar(max), @count int

set @count = 0

declare curseur cursor for SELECT top 1 view_definition FROM prod.information_schema.VIEWS 
open curseur
fetch curseur into  @view_definition
While @@FETCH_STATUS = 0
Begin
    set @count = @count + 1
    --Print 'Vue N° ' + cast(@count as varchar) + ':'
    set @Query = N'Use ' + @database2 +CHAR(13)+CHAR(10)+ 'go' + @view_definition +CHAR(13)+CHAR(10)+ 'go'
    print @Query
    exec sp_executesql @Query
    fetch curseur into  @view_definition
End
Close curseur
deallocate curseur

此代码是从database1执行的.

This code was executed from database1.

但是当我执行'print @Query'指令的结果时，它就可以了！

However when I execute the result of the 'print @Query' instruction, it works!!

有人可以帮助我解决问题吗?

Does anyone can help me to resolve the problem?

预先感谢

推荐答案

以防万一将来对某人有帮助，这是我针对此问题的解决方案.有关如何提出该命令的完整历史，请参见将视图从当前数据库复制到另一个数据库的存储过程

In case this helps someone in the future, here's my solution to this problem. The full history of how I came up with it is over at Stored Procedure to Copy Views from Current Database to Another

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:

In short, you need two layers of nested dynamic SQL:

• 执行创建视图"的内层；声明，该声明必须是独立的.这是使用 EXEC SomeDatabase.sys.sp_executesql @CreateViewSQL
执行的
• 动态地指定"SomeDatabase"的另一层.通过参数(假设您需要将其复制到脚本编写时未知的数据库中的灵活性).

在原始发布者的暂定解决方案的内部循环中调用上述存储的proc应该可以解决将视图复制到另一个数据库的问题.

Calling the above stored proc in the inner-loop of the original poster's tentative solution should solve the problem of copying a view to another database.

请注意，如果视图彼此依赖，则仅循环浏览所有视图可能会带来挑战.可能存在一些其他复杂性，其中包括解析视图的依赖关系树并以正确的顺序复制视图.可替代地，简单而又简单"的解决方案包括:方法可能是遍历所有视图，忽略故障，并不断重复该过程，直到创建完所有视图为止.

Note that simply looping over all views might pose a challenge if views depend on one another. There may be some additional complexity involving resolving the dependency tree of views an copying them in the correct order. Alternatively, a "dumb and easy" approach might be to loop over all views, ignore failures, and keep repeating the process until all views have been created.

这篇关于将视图定义从一个数据库复制到SQL Server中的另一个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！