--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;
ENDThis script synchronize all stored procedures between two servers by using spSynchStoredProcedure.
