SQL Server 发布订阅管理

1. 查看已发布的项目

USE $DBName$;
GO

EXEC sys.sp_helparticle @publication = '$publication$', @article = '%';

2. 添加发布项目

目前支持：用户表、视图。

USE $TargetDatabase$;
GO

DECLARE @targets TABLE (sch VARCHAR(200), obj VARCHAR(500), type CHAR(2), typeDesc VARCHAR(200));
INSERT @targets (sch, obj, type, typeDesc)
SELECT    t.sch
        , t.obj
        , obj.type
        , CASE obj.type
               WHEN 'AF' THEN N'聚合函数 (CLR)'
               WHEN 'C' THEN N'CHECK 约束'
               WHEN 'D' THEN N'默认值或 DEFAULT 约束'
               WHEN 'F' THEN N'FOREIGN KEY 约束'
               WHEN 'L' THEN N'日志'
               WHEN 'FN' THEN N'标量函数'
               WHEN 'FS' THEN N'程序集 (CLR) 标量函数'
               WHEN 'FT' THEN N'程序集 (CLR) 表值函数'
               WHEN 'IF' THEN N'内联表函数'
               WHEN 'IT' THEN N'内部表'
               WHEN 'P' THEN N'存储过程'
               WHEN 'PC' THEN N'程序集 (CLR) 存储过程'
               WHEN 'PK' THEN N'PRIMARY KEY 约束'
               WHEN 'RF' THEN N'复制筛选存储过程'
               WHEN 'S' THEN N'系统表'
               WHEN 'SN' THEN N'同义词'
               WHEN 'SQ' THEN N'服务队列'
               WHEN 'TA' THEN N'程序集 (CLR) DML 触发器'
               WHEN 'TF' THEN N'表函数'
               WHEN 'TR' THEN N'SQL DML 触发器'
               WHEN 'TT' THEN N'表类型'
               WHEN 'U' THEN N'用户表'
               WHEN 'UQ' THEN N'UNIQUE 约束（类型为 K）'
               WHEN 'V' THEN N'视图'
               WHEN 'X' THEN N'扩展存储过程'
               ELSE CONCAT(N'未知（', obj.type, N'）')
          END
FROM      (
  SELECT CASE WHEN t.dotIdx > 1 THEN LEFT(t.fullname, t.dotIdx - 1)ELSE 'dbo' END sch
       , CASE WHEN t.dotIdx > 0 THEN RIGHT(t.fullname, LEN(t.fullname) - t.dotIdx)ELSE t.fullname END obj
  FROM   (SELECT t.fullname, CHARINDEX('.', t.fullname) dotIdx FROM (VALUES ('$TargetObjectName$')) t (fullname) ) t
) t
LEFT JOIN sys.schemas sch WITH (NOLOCK) ON sch.name = t.sch
LEFT JOIN sys.objects obj WITH (NOLOCK) ON obj.schema_id = sch.schema_id AND obj.name = t.obj;

IF EXISTS (SELECT 1 FROM @targets t WHERE t.typeDesc LIKE '未知%')
BEGIN
  RAISERROR(N'存在异常对象', 16, 1);
  RETURN;
END;

BEGIN
  DECLARE @sch VARCHAR(200), @obj VARCHAR(500), @type CHAR(2), @typeDesc VARCHAR(200);

  DECLARE targetCur CURSOR FAST_FORWARD READ_ONLY FOR SELECT t.sch, t.obj, t.type, t.typeDesc FROM @targets t;
  OPEN targetCur;

  FETCH NEXT FROM targetCur
  INTO @sch, @obj, @type, @typeDesc;

  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @typeDesc = '用户表'
    BEGIN
      EXEC sys.sp_addarticle @publication = N'$PublicationName$'
                           , @article = @obj
                           , @source_owner = @sch
                           , @source_object = @obj
                           , @type = N'logbased'
                           , @description = null
                           , @creation_script = null
                           , @pre_creation_cmd = N'none'
                           , @schema_option = 0x000000000803509F
                           , @force_invalidate_snapshot = 1
                           , @identityrangemanagementoption = N'manual'
                           , @destination_table = @obj
                           , @destination_owner = @sch
                           , @vertical_partition = N'false'
                           , @ins_cmd = NULL
                           , @del_cmd = NULL
                           , @upd_cmd = NULL;
    END;
    ELSE IF @typeDesc = '视图'
    BEGIN
      EXEC sys.sp_addarticle @publication = N'$PublicationName$'
                           , @article = @obj
                           , @source_owner = @sch
                           , @source_object = @obj
                           , @type = N'view schema only'
                           , @description = null
                           , @creation_script = null
                           , @pre_creation_cmd = N'drop'
                           , @schema_option = 0x0000000008000001
                           , @force_invalidate_snapshot = 1
                           , @destination_table = @obj
                           , @destination_owner = @sch;
    END;
    ELSE
    BEGIN
      PRINT CONCAT('>> error: ', @sch, '.', @obj, ': 不支持的类型（', @typeDesc, '）');
    END;

    FETCH NEXT FROM targetCur
    INTO @sch, @obj, @type, @typeDesc;
  END;

  CLOSE targetCur;
  DEALLOCATE targetCur;
END;

EXEC sys.sp_startpublication_snapshot @publication = N'$PublicationName$';

DECLARE @polling INT = 1, @pollingTimes INT = 0;
WHILE @polling = 1 AND @pollingTimes < $PollingTimes$
BEGIN
  WAITFOR DELAY '$Delay$';

  DECLARE @lastRunStates NVARCHAR(200);
  SELECT      @lastRunStates = CASE jobhis.run_status
                                    WHEN 0 THEN N'失败'
                                    WHEN 1 THEN N'成功'
                                    WHEN 2 THEN N'重试'
                                    WHEN 3 THEN N'取消'
                                    WHEN 4 THEN N'进行中'
                                    ELSE N'未知（' + CONVERT(NVARCHAR(20), jobhis.run_status) + N'）'
                               END
  FROM        msdb..sysjobs job
  INNER JOIN  msdb..sysjobsteps jobstep ON jobstep.job_id = job.job_id
  OUTER APPLY (
    SELECT   TOP 1
             jobhis.message, jobhis.run_status, jobhis.run_date, jobhis.run_time, jobhis.run_duration
    FROM     msdb..sysjobhistory jobhis
    WHERE    jobhis.job_id = job.job_id AND jobhis.step_id = jobstep.step_id
    ORDER BY jobhis.instance_id DESC
  ) jobhis
  WHERE       job.enabled = 1 AND job.name LIKE '%$PublicationJobName$%' AND jobstep.subsystem = 'Snapshot';

  IF @@ROWCOUNT <> 1
  BEGIN
    RAISERROR(N'查询快照代理执行状态失败', 16, 1);
    RETURN;
  END;

  SET @polling = CASE WHEN @lastRunStates = N'成功' THEN 0 ELSE 1 END;
  SET @pollingTimes = @pollingTimes + 1;
END;

EXEC sys.sp_refreshsubscriptions @publication = N'$PublicationName$';