T-SQL Tips and Tricks: Copying Stored Procedure or Function from one database to multiple target databases

If you are a SQL Database Developer or SQL Database Administrator then you may run into this situation. You have created a Stored Procedure or Routine and you want 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 routines from one DB to another DB. Following is the script, that 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_routine
		-- copies a stored procedure or function from current database to target database(s)
	example:
		exec sharpquest.copy_routine 'dbo.my_sp_name', 'customer_db'
		-- above will copy the sp with name my_sp_name to a target database customer_db on same sql server that you have access to 	
exec sharpquest.copy_routine 'dbo.my_sp_name', 'qa_'
		-- above will copy the sp with name my_sp_name to all databases 
whose name starts with [qa_] on same sql server that you have access to 
*/
CREATE OR ALTER PROCEDURE sharpquest.copy_routine(  
 @routineName varchar(100),  
 @targetDBPrefix varchar(10)  
)  
AS   
BEGIN   

 DECLARE @msg varchar(1000)

 IF (object_id(@routineName) IS NULL)  
 BEGIN  
	SET @msg = 'Object_id for SP Or Function ' + @routineName + ' Not Found in current database ' + db_name()
	RAISERROR (@msg, -- Message text.      
		16, -- Severity.      
		1 -- State.      
		);    
	RETURN  
 END     

 declare @rtype varchar(100)  
 declare @dropSql varchar(max)   
 declare @sql varchar(max)   
   
 SELECT @rtype = [type_desc] 
 FROM sys.all_objects 
 WHERE object_id = object_id(@routineName)
 AND ([type_desc] LIKE '%FUNCTION%'
 OR [type_desc] LIKE '%PROCEDURE%')

 IF (@rtype IS NULL)  
 BEGIN  
	SET @msg = 'Type for SP Or Function ' + @routineName + ' Not Found in current database ' + db_name()
	RAISERROR (@msg, -- Message text.      
		16, -- Severity.      
		1 -- State.      
		);    
	RETURN  
 END    

 PRINT @rtype

 IF (@rtype LIKE '%FUNCTION%') SET @rtype = 'FUNCTION'
 ELSE SET @rtype = 'PROCEDURE'

 PRINT @rtype

 select @sql = object_definition(object_id(@routineName))
 
 SET @dropSql = 'DROP ' + @rtype + ' IF EXISTS ' + @routineName + ';';  
 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;    

	BEGIN TRY 
		exec sp_executesql @curSQL     
	    PRINT '-- Routine Created in -- ' + @currentName   
	END TRY 
	BEGIN CATCH 
	   PRINT @currentName + '-- ERROR ---' + ERROR_MESSAGE()    
	   PRINT '-- SQL -- '     
	   PRINT @curSQL     
	END CATCH 
  
    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