Synchronize all stored procedures between two servers

,

Synchronize all stored procedures between two servers by using spSynchStoredProcedure.

--Description: synchronize all stored procedures between two servers
--		@ACTION = 0: Synch procedures exist in server1 but not in server2
--		@ACTION = 1: Synch all procedures from server1 to server2
--WARNING: use this stored procedure VERY CAREFULLY!
CREATE PROCEDURE [dbo].[spSynchAllProcedures]
	@ACTION TINYINT = 0, 
	@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 @ProcedureName VARCHAR(50);
	
	--Insert to temp table since some stored procedures contains over 4000 characters
	CREATE TABLE #tblTmp(item VARCHAR(50));
	IF @ACTION = 0
		SET @STRSQL = N'INSERT INTO #tblTmp
					SELECT [name]
					FROM ' + @SourceServer + '.' + @SourceDatabase + '.dbo.sysobjects
					WHERE xtype = ''P''
					AND [name] NOT IN (SELECT [name] 
									FROM ' + @TargetServer + '.' + @TargetDatabase + '.dbo.sysobjects
									WHERE xtype = ''P'')';
	ELSE IF @ACTION = 1
		SET @STRSQL = N'INSERT INTO #tblTmp
					SELECT [name]
					FROM ' + @SourceServer + '.' + @SourceDatabase + '.dbo.sysobjects
					WHERE xtype = ''P''';

	EXECUTE(@STRSQL);
	
	DECLARE Cur CURSOR FOR
		SELECT item FROM #tblTmp
	OPEN Cur
	FETCH FROM Cur
	INTO @ProcedureName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--PRINT @ProcedureName;		
		EXEC dbo.spSynchStoredProcedure @ProcedureName, @SourceServer, @SourceDatabase, @TargetServer, @TargetDatabase;

		FETCH NEXT FROM Cur
		INTO @ProcedureName
	END
	CLOSE Cur
	DEALLOCATE Cur

	DROP TABLE #tblTmp;
END

Rate

2.67 (3)

Share

Share

Rate

2.67 (3)