How to Sync all the stored procedures in between two servers


–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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: