CREATE PROCEDURE sys.sp_MScreate_peer_tables
AS
BEGIN
    DECLARE @retcode bit

    begin transaction tran_sp_MScreate_peer_tables
    save transaction tran_sp_MScreate_peer_tables

    IF OBJECT_ID(N'MSpeer_lsns', 'U') is NULL
    BEGIN
        CREATE TABLE dbo.MSpeer_lsns
        (
            id                         	int identity(1,1) primary key nonclustered,
            last_updated               	datetime default getdate(),
            originator                  sysname,
            originator_db               sysname,
            originator_publication      sysname,
            originator_publication_id   int,
            originator_db_version       int,
            originator_lsn              varbinary(16),
            originator_version	    int NULL, --server build version of the peer
            originator_id		    int NULL -- 4 bytes are used
        )
        IF @@ERROR <> 0
            GOTO UNDO

        EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_lsns'
        IF @@ERROR <> 0 or @retcode <> 0
            GOTO UNDO

        CREATE UNIQUE CLUSTERED INDEX uci_MSpeer_lsns
    	ON dbo.MSpeer_lsns
    	(
			originator,
			originator_db,
			originator_publication_id,
			originator_db_version,
			originator_lsn
    	)
    END

    IF EXISTS (select * from sys.objects where name = 'syspublications')
    BEGIN
    	    IF OBJECT_ID(N'MSpeer_request', 'U') is NULL
	    BEGIN
	        CREATE TABLE dbo.MSpeer_request
       	 (
	            id            int    identity(1,1),
	            publication    sysname,
	            sent_date    datetime default getdate(),
	            description    nvarchar(4000)
       	 )
	        IF @@ERROR <> 0
       	     GOTO UNDO

	        EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_request'
       	 IF @@ERROR <> 0 or @retcode <> 0
	            GOTO UNDO
	    END

	    IF OBJECT_ID(N'MSpeer_response', 'U') is NULL
	    BEGIN
       	 CREATE TABLE dbo.MSpeer_response
	        (
       	     request_id        int,
	            peer            sysname,
	            peer_db            sysname,
	            received_date    datetime NULL
       	 )
	        IF @@ERROR <> 0
       	     GOTO UNDO

	        EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_response'
	        IF @@ERROR <> 0  or @retcode <> 0
       	     GOTO UNDO
	    END

	    IF OBJECT_ID(N'MSpeer_topologyrequest', 'U') is NULL
	    BEGIN
       	 CREATE TABLE dbo.MSpeer_topologyrequest
	        (
       	     id            int    identity(1,1),
	            publication    sysname,
       	     sent_date    datetime default getdate()
	        )
	        IF @@ERROR <> 0
       	     GOTO UNDO

	        EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_topologyrequest'
	        IF @@ERROR <> 0  or @retcode <> 0
       	     GOTO UNDO
	    END

	    IF OBJECT_ID(N'MSpeer_topologyresponse', 'U') is NULL
	    BEGIN
	        CREATE TABLE dbo.MSpeer_topologyresponse
       	 (
	            request_id        int,
	            peer            sysname,
	            peer_version	int NULL,  --server build version of the peer
	            peer_db            sysname,
	            originator_id	int  NULL,  -- 4  bytes are used
	            peer_conflict_retention int NULL,
	            received_date    datetime NULL,
	            connection_info    XML
       	 )
	        IF @@ERROR <> 0
       	     GOTO UNDO

	        EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_topologyresponse'
	        IF @@ERROR <> 0  or @retcode <> 0
       	     GOTO UNDO
	    END


	    IF OBJECT_ID(N'MSpeer_originatorid_history', 'U') is NULL
	    BEGIN
	        CREATE TABLE dbo.MSpeer_originatorid_history
	        (
	            originator_publication sysname,
	            originator_id		int not null, -- 4 bytes are used
	            originator_node        sysname,
	            originator_db            sysname,
	            originator_db_version int not null,
	            originator_version	int not null,  --server build version of the peer
		     inserted_date    datetime not null default getdate()
	        )
	        IF @@ERROR <> 0
       	     GOTO UNDO

	        EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_originatorid_history'
	        IF @@ERROR <> 0 or @retcode <> 0
       	     GOTO UNDO

	       CREATE UNIQUE CLUSTERED INDEX uci_MSpeer_originatorid_history
	    	ON dbo.MSpeer_originatorid_history
	    	(
	    		originator_publication,
	    		originator_id,
	    		originator_node,
	    		originator_db,
	    		originator_db_version
    		)
	    END

	    IF OBJECT_ID(N'MSpeer_conflictdetectionconfigrequest', 'U') is NULL
	    BEGIN
	        CREATE TABLE dbo.MSpeer_conflictdetectionconfigrequest
	        (
	            id					int   identity(1,1) not null primary key,
	            publication				sysname,
	            sent_date				datetime not null default getdate(),
	            timeout				int not null, --seconds
	            modified_date			datetime not null default getdate(),
	            progress_phase			nvarchar(32) not null,
	            phase_timed_out		bit not null
	        )
	        IF @@ERROR <> 0
       	     GOTO UNDO

	        EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_conflictdetectionconfigrequest'
	        IF @@ERROR <> 0 or @retcode <> 0
       	     GOTO UNDO
   		END

	  IF OBJECT_ID(N'MSpeer_conflictdetectionconfigresponse', 'U') is NULL
	  BEGIN
       	CREATE TABLE dbo.MSpeer_conflictdetectionconfigresponse
	        (
       	     request_id				int not null,
	            peer_node			       sysname,
	            peer_db				sysname,
	            peer_version			int NULL,  --server build version of the peer
	            peer_db_version		int NULL,
       	     is_peer				bit NULL, --once set, later rounds would expect response from it
	            conflictdetection_enabled bit NULL,
	            originator_id			int NULL,
	            peer_conflict_retention 	int NULL,
	            peer_continue_onconflict  bit NULL,
	            peer_subscriptions		xml NULL, --list of (sub_node, sub_db)
	            progress_phase			nvarchar(32) not null,
		     modified_date			datetime default getdate()
       	 )
	        IF @@ERROR <> 0
       	     GOTO UNDO

	        EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_conflictdetectionconfigresponse'
       	 IF @@ERROR <> 0 or @retcode <> 0
	            GOTO UNDO

	       CREATE UNIQUE CLUSTERED INDEX uci_MSpeer_conflictdetectionconfigresponse
	    	ON dbo.MSpeer_conflictdetectionconfigresponse
	    	(
    			request_id,
			peer_node,
            		peer_db
    		)
      END
    END

	--add conflict detection alert on subscriber
    declare @alert_name nvarchar(max) = formatmessage(22827)
				,@alert_id int = 22815
				,@category_name sysname

 	SELECT	@category_name = name
    	FROM	msdb.dbo.syscategories
    	WHERE	category_id = 20

    IF NOT EXISTS
    (
    	SELECT	*
    	FROM	msdb.dbo.sysalerts
    	WHERE	message_id = @alert_id
    )
    BEGIN
        EXEC @retcode = msdb.dbo.sp_add_alert	@enabled = 0,
        										@name = @alert_name,
        										@category_name = @category_name,
        										@message_id = @alert_id

        IF (@@ERROR != 0 OR @retcode != 0)
        BEGIN
             goto UNDO
        END
    END


    commit transaction tran_sp_MScreate_peer_tables

    return 0
UNDO:
    rollback transaction tran_sp_MScreate_peer_tables
    commit transaction tran_sp_MScreate_peer_tables

    return 1
END