Synchronize a stored procedure between two servers

,

Sometimes I need to synchonize source codes between development and production server. So, I created this stored procedure to help me do that instead of generating scripts from one server and execute it in another server.

To run this stored procedure, you need to create two linked servers for your source server and your target server. You can create them in local SQL Server and then run this to synchronize.

For example, your development server is called DEV and your production server is called PROD, and you create two linked servers in local SQL Server with the same name. Assumed that your database between DEV and PROD have the same name: TestDB.

If you want to synchronize TestProc stored procedure between TestDB of DEV and PROD, you can excute like that:

EXEC @ProcedureName = 'TestProc', @SourceServer = 'DEV', @SourceDatabase = 'TestDB', @TargetServer = 'PROD', @TargetDatabase = 'TestDB'

CREATE PROCEDURE [dbo].[spSynchStoredProcedure]
	@ProcedureName VARCHAR(50) = NULL,
	@SourceServer VARCHAR(50) = NULL,
	@SourceDatabase VARCHAR(50) = NULL,
	@TargetServer VARCHAR(50) = NULL,
	@TargetDatabase VARCHAR(50) = NULL
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @STRSQL NVARCHAR(MAX);
	DECLARE @Params NVARCHAR(MAX);
	DECLARE @IsExisted BIT;
	DECLARE @ProcedureSource NVARCHAR(MAX);
	DECLARE @Tmp NVARCHAR(MAX);
	SET @IsExisted = 0;
	SET @STRSQL = N' SELECT @IsExisted = 1
					FROM ' + @TargetServer + '.' + @TargetDatabase + '.sys.objects
					WHERE name = ''' + @ProcedureName + ''' AND Type = ''P''';
	SET @Params = N'@IsExisted BIT OUTPUT';
	EXEC sp_executesql @STRSQL, @Params, @IsExisted = @IsExisted OUTPUT
	IF (@IsExisted = 1)
	BEGIN
		SET @STRSQL = N'EXEC ' + @TargetServer + '.' + @TargetDatabase + '.dbo.sp_executesql N''DROP PROCEDURE ' + @ProcedureName + '''';
		--PRINT @STRSQL;
		EXECUTE(@STRSQL);
	END;
	
	--Insert to temp table since some stored procedures contains over 4000 characters
	CREATE TABLE #tblTmp(item NVARCHAR(MAX));
	SET @STRSQL = N'INSERT INTO #tblTmp
					SELECT t1.text 
					FROM ' + @SourceServer + '.' + @SourceDatabase + '.dbo.syscomments t1
					INNER JOIN ' + @SourceServer + '.' + @SourceDatabase + '.dbo.sysobjects t2 on t1.id = t2.id
					WHERE t2.xtype = ''P''
					AND t2.name = ''' + @ProcedureName + '''';
	EXECUTE(@STRSQL);
	
	SET @ProcedureSource = '';
	DECLARE MyCursor CURSOR FOR
		SELECT item FROM #tblTmp
	OPEN MyCursor
	FETCH FROM MyCursor
	INTO @Tmp
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @ProcedureSource = @ProcedureSource + @Tmp;
		FETCH NEXT FROM MyCursor
		INTO @Tmp
	END
	CLOSE MyCursor
	DEALLOCATE MyCursor

	DROP TABLE #tblTmp;
	--PRINT @ProcedureSource;

	SET @ProcedureSource = REPLACE(@ProcedureSource, '''', '''''');
	SET @STRSQL = N'EXEC ' + @TargetServer + '.' + @TargetDatabase + '.dbo.sp_executesql N''' + @ProcedureSource + '''';
	--PRINT @ProcedureSource;
	--PRINT @STRSQL;
	EXECUTE(@STRSQL);
END

Rate

3 (2)

Share

Share

Rate

3 (2)