T-SQL Tips and Tricks: Copying Views from one database to multiple target databases

If you are a MS SQL Database developer or Database Administrator then you may run into this situation. You have created a SQL View and you have to make it available in multiple databases. A simple way is to open each database and run your script.

Our team at SharpQuest maintains more than dozen databases for each environment - development, QA, Preproduction and production. And executing scripts in multiple databases during development or testing becomes a time consuming and many times manual process.

To make our developers' life easy we have created few routines that comes handy to the DBA to copy views and routines from one DB to another DB. 

If you need a script to copy [Stored Procedure or Functions] from one database to another then follow this article. https://www.sharpquest.com/blog/posts/2024/march/t-sql-copying-stored-procedure-or-function-from-one-database-to-multiple-target-databases/

Following is the script to copy VIEW from one SQL DB to another. You can modify and use for your own purpose. If you modify with features that can be useful to others, please comment here or share with us so.

/*
	sharpquest.copy_view
		-- copies a SQL View from current database to target database(s)
	example:
		exec sharpquest.copy_view 'dbo.vCustomer', 'customer_db'
		-- above will copy the view vCustomer to a target database customer_db on same sql server that you have access to 
		exec sharpquest.copy_view 'dbo.vCustomer', 'qa_'
		-- above will copy the view vCustomer to all databases whose name starts with [qa_] on same sql server that you have access to 
*/    
CREATE OR ALTER   PROCEDURE sharpquest.copy_view(    
 @viewName varchar(100),    
 @targetDBPrefix varchar(10)    
)    
AS     
BEGIN     
     
 declare @rtype varchar(20)    
 declare @dropSql varchar(max)     
 declare @sql varchar(max)     
    
 set @sql =  object_definition(object_id(@viewName))   
    
 IF (@Sql IS NULL)    
 BEGIN    
  RAISERROR ('View Not Found', -- Message text.        
     16, -- Severity.        
     1 -- State.        
     );      
  RETURN    
 END    
    
 SET @dropSql = 'DROP VIEW IF EXISTS ' + @viewName + ';';    
 SET @sql = @sql;    
    
  DECLARE cur_dbname CURSOR FOR      
   SELECT [name]     
   FROM sys.databases     
   where [name] like @targetDBPrefix + '%'      
   AND [name] NOT LIKE '%_global'      
   AND [name] NOT LIKE '%_users'      
   AND [name] <> db_name()    
    
  DECLARE @currentName varchar(100)      
  DECLARE @curSQL nvarchar(max)       
      
  OPEN cur_dbname       
  FETCH NEXT FROM cur_dbname INTO @currentName       
      
  SET @dropSql = 'EXEC(''' + REPLACE(@dropSql, '''', '''''') + ''')'      
  SET @SQL = 'EXEC(''' + REPLACE(@SQL, '''', '''''') + ''')'      
      
  BEGIN TRY       
   WHILE @@FETCH_STATUS = 0       
   BEGIN      
    SET @curSQL = ';use ' + @currentName + ';'       
    -- PRINT @curSQL;      
    
    SET @curSQL = @curSQL  + '; ' + CHAR(10) + @dropSql + CHAR(10) + @SQL;      
    exec sp_executesql @curSQL       
        
    PRINT '-- VIEW Created in -- ' + @currentName     
    
    FETCH NEXT FROM cur_dbname INTO @currentName       
   END       
  END TRY       
  BEGIN CATCH       
   PRINT '-- ERROR ---' + ERROR_MESSAGE()      
   PRINT '-- SQL -- '       
   PRINT @curSQL       
  END CATCH       
       
  CLOSE cur_dbname       
  DEALLOCATE cur_dbname       
END