Stored Procedure to Copy Views from Current Database to Another

I’ve been asked whether it’s possible to create a stored procedure that will copy all views in the current database to another one (named via stored procedure parameter).

For context, all databases have the same schemas. This situation arises thanks to a 3rd party risk modelling tool that generates each run’s output as an entirely new database (rather than additional rows in an existing database). The user wants an easy way to “apply” their 20 or so custom views (from their “Template” database) to another identical database on-demand. They wish to maintain the “latest version” of the views in one database, and then “Update” (Drop + Create) views on any other database by executing this stored procedure. As far as I can tell, this ask is almost identical to the ask in Copy a view definition from one database to another one in SQL Server, which never got an answer.

---

Where I’ve gotten so far:

• Getting a view definition: Easy

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

  The question at Copy a view definition from one database to another one in SQL Server even has code for iteratively getting the definitions of all views.

• Passing in a database name as a parameter: Medium

  Not knowing the target database name at script creation time is hard. As far as I know, this guarantees that you will be relying on Dynamic SQL (EXEC) to do whatever you’re doing.

• Creating a view on another database: Hard

  You can’t just add USE [OtherDatabase] to the start of some dynamic CREATE VIEW statement – this yields the error “CREATE VIEW must be the first statement in a query batch.“. And you can’t just add a GO statement in there either – the error Incorrect syntax near ‘GO' serves as a reminder that this is not valid TSQL. A blog post I found solved the issue by invoking

  EXEC [SomeOtherDatabase].dbo.sp_executesql @CreateViewSQL
  

  But unfortunately, this solution can’t be used in the context where ‘SomeOtherDatabase’ is intended to be passed in as an argument.

• This took me to an incredibly awkward situation of having to construct and execute a dynamic SQL statement from within another dynamic SQL statement.

So currently my proof-of-concept solution looks like this:

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);

I couldn’t find anything else like it online, but surprisingly (to me) it works. “SampleView” gets copied over to the new database. I can now expand on this concept to copy over all views. But before I go any further…

---

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?

Answer

I think I found a marginally better way (one that will make a bigger difference as I add complexity to this routine).

The main issue with my original implementation:

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);

Is that the inner-dynamic-execution of dbo.sp_executesql @ViewDefinition requires the view creation code @ViewDefinition to be derived within the dynamic SQL block. This code is simple for now (a single hard-coded view name being extracted) – but my plans are to expand on this to loop over all views in the database and copy them all. I would rather not have to code up this logic within the dynamic SQL block.

Ideally, I wanted to have this logic live in the main stored procedure space, and only pass in the resulting CREATE VIEW command to the dynamic SQL, but simply concatenating the contents of the CREATE VIEW command as returned by definition would result in quote escaping issues. The workaround was to manually escape the returned SQL code so that it could be subsequently ‘unescaped’ by the inner dynamic SQL execution:

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);

While it looks like a bit more code, the main difference is now the guts of the logic are outside of the dynamic SQL (which is only required to execute that logic against an arbitrary database). Assuming single quotes are the only escaping mechanism I have to worry about here, I think this will let me blast ahead with building out logic to iterate over all views, set up DROP commands if needed, etc, without having to escape everything from the offset. I’ll post back with the full solution when I have it.

---

Alright, here’s is my full working solution:

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);

Note: Some of the escape sequences break syntax highlighting here, it’s not as bad as it looks in terms of dynamic SQL statements: I submitted a bug to highlight.js (currently used by StackOverflow), but it turns out it’s actually SO’s fault for disabling certain languages (tsql in this case) – so perhaps upvote this meta.stackexchange post if that annoys you too 🙂

The solution can be used in the following way:

EXECUTE [dbo].[usp_Copy_View_To_Database] 'SampleView', 'SomeOtherDatabase'

I decided that the above is an ‘appropriate’ chunk of work for a stored procedure – copying a single view. Multiple views can be copied over via repeated calls to this stored proc, but I didn’t necessarily want to automate that here, since other folks might have a subset they want to copy, or interdependencies that make the copy order non-trivial.

Note that a chunk of the code is a warning check for a rather annoying problem wherein SQL can have the wrong name for a renamed view saved in the definition behind-the-scenes. If I stumble on an elegant way of dealing with this, I might update it further.