Stored Procedure to Copy Views from Current Database to Another

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?

No. If you want to do this with TSQL, this is the way.

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.