SQL Server 2005 复制：用 SQL 语句快速查看发布（publication）都有哪些订阅（subscription），以及发布包含了哪些表

131 阅读 0 评论 87 点赞

我是靠谱客的博主整齐曲奇，这篇文章主要介绍SQL Server 2005 复制：用 SQL 语句快速查看发布（publication）都有哪些订阅（subscription），以及发布包含了哪些表，现在分享给大家，希望可以做个参考。

公司的数据库服务器上建立了 N 多个复制（replication），大大小小的发布（publication）都有上百个。每当数据库复制同步出现问题的时候，最头痛了，因为要查看这个发布包含了哪些表，特别是当查找某个表都包含在哪些发布中，以及被哪些数据库服务器订阅（subscription），在 SQL Server 2005 的复制监视器（replication monitor）中去找到需要花费很多时间。于是我写了个 SQL 脚本，可以方便的查看数据库复制中的发布、订阅、出版物（article）等基本信息。利用这个 SQL 脚本，可以查看：

复制代码

1
2
3
4
1. 当前数据库中都有哪些发布。
2. 一个发布中包含了哪些表。
3. 发布都是被哪些数据库服务器订阅。

复制代码

--------------------------------------------------------------------------------
-- author : p.c.w.l
-- source : www.sqlstudy.com
-- create : 2008-01-01
-- descr  : a simple sql script to view base-info of publication, subscription
--------------------------------------------------------------------------------

select publication       = p.name
      ,pub_status        = case p.status when 0 then 'inactive' when 1 then 'active' end
      ,sysn_method       = case p.sync_method
                                when 0 then 'native bcp'
                                when 1 then 'character bcp'
                                when 3 then 'native bcp (nolock table)'
                                when 4 then 'character bcp (nolock table)'
                           end
      ,article           = a.name
      ,art_local_table   = object_name(a.objid)
      ,art_remote_table  = a.dest_owner + '.' + a.dest_table
      ,art_type          = case a.type
                              when 1   then '1 = log-based article'
                              when 3   then '3 = log-based article with manual filter'
                              when 5   then '5 = log-based article with manual view'
                              when 7   then '7 = log-based article with manual filter and manual view'
                              when 8   then '8 = stored procedure execution'
                              when 24  then '24 = serializable stored procedure execution'
                              when 32  then '32 = stored procedure (schema only)'
                              when 64  then '64 = view (schema only)'
                              when 128 then '128 = function (schema only)'
                           end
      ,art_status        = case when a.status & 1 = 1 then 'active' else 'inactive' end
      ,subserver         = s.srvname
      ,subdb             = s.dest_db
  from dbo.syspublications p 
          inner join dbo.sysarticles a
    on p.pubid = a.pubid 
          inner join dbo.syssubscriptions s
    on a.artid = s.artid and s.srvid > 0
 where 1 = 1
-- and p.name               = ''    -- publication name
-- and a.name               = ''    -- article name (note:article name always same with table name)
-- and object_name(a.objid) = ''    -- table name
--order by s.srvname, a.name
order by p.name

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
复制代码--------------------------------------------------------------------------------
-- author : p.c.w.l
-- source : www.sqlstudy.com
-- create : 2008-01-01
-- descr  : a simple sql script to view base-info of publication, subscription
--------------------------------------------------------------------------------

select publication       = p.name
      ,pub_status        = case p.status when 0 then 'inactive' when 1 then 'active' end
      ,sysn_method       = case p.sync_method
                                when 0 then 'native bcp'
                                when 1 then 'character bcp'
                                when 3 then 'native bcp (nolock table)'
                                when 4 then 'character bcp (nolock table)'
                           end
      ,article           = a.name
      ,art_local_table   = object_name(a.objid)
      ,art_remote_table  = a.dest_owner + '.' + a.dest_table
      ,art_type          = case a.type
                              when 1   then '1 = log-based article'
                              when 3   then '3 = log-based article with manual filter'
                              when 5   then '5 = log-based article with manual view'
                              when 7   then '7 = log-based article with manual filter and manual view'
                              when 8   then '8 = stored procedure execution'
                              when 24  then '24 = serializable stored procedure execution'
                              when 32  then '32 = stored procedure (schema only)'
                              when 64  then '64 = view (schema only)'
                              when 128 then '128 = function (schema only)'
                           end
      ,art_status        = case when a.status & 1 = 1 then 'active' else 'inactive' end
      ,subserver         = s.srvname
      ,subdb             = s.dest_db
  from dbo.syspublications p 
          inner join dbo.sysarticles a
    on p.pubid = a.pubid 
          inner join dbo.syssubscriptions s
    on a.artid = s.artid and s.srvid > 0
 where 1 = 1
-- and p.name               = ''    -- publication name
-- and a.name               = ''    -- article name (note:article name always same with table name)
-- and object_name(a.objid) = ''    -- table name
--order by s.srvname, a.name
order by p.name复制代码--------------------------------------------------------------------------------
-- author : p.c.w.l
-- source : www.sqlstudy.com
-- create : 2008-01-01
-- descr  : a simple sql script to view base-info of publication, subscription
--------------------------------------------------------------------------------

select publication       = p.name
      ,pub_status        = case p.status when 0 then 'inactive' when 1 then 'active' end
      ,sysn_method       = case p.sync_method
                                when 0 then 'native bcp'
                                when 1 then 'character bcp'
                                when 3 then 'native bcp (nolock table)'
                                when 4 then 'character bcp (nolock table)'
                           end
      ,article           = a.name
      ,art_local_table   = object_name(a.objid)
      ,art_remote_table  = a.dest_owner + '.' + a.dest_table
      ,art_type          = case a.type
                              when 1   then '1 = log-based article'
                              when 3   then '3 = log-based article with manual filter'
                              when 5   then '5 = log-based article with manual view'
                              when 7   then '7 = log-based article with manual filter and manual view'
                              when 8   then '8 = stored procedure execution'
                              when 24  then '24 = serializable stored procedure execution'
                              when 32  then '32 = stored procedure (schema only)'
                              when 64  then '64 = view (schema only)'
                              when 128 then '128 = function (schema only)'
                           end
      ,art_status        = case when a.status & 1 = 1 then 'active' else 'inactive' end
      ,subserver         = s.srvname
      ,subdb             = s.dest_db
  from dbo.syspublications p 
          inner join dbo.sysarticles a
    on p.pubid = a.pubid 
          inner join dbo.syssubscriptions s
    on a.artid = s.artid and s.srvid > 0
 where 1 = 1
-- and p.name               = ''    -- publication name
-- and a.name               = ''    -- article name (note:article name always same with table name)
-- and object_name(a.objid) = ''    -- table name
--order by s.srvname, a.name
order by p.name--------------------------------------------------------------------------------
-- author : p.c.w.l
-- source : www.sqlstudy.com
-- create : 2008-01-01
-- descr  : a simple sql script to view base-info of publication, subscription
--------------------------------------------------------------------------------

select publication       = p.name
      ,pub_status        = case p.status when 0 then 'inactive' when 1 then 'active' end
      ,sysn_method       = case p.sync_method
                                when 0 then 'native bcp'
                                when 1 then 'character bcp'
                                when 3 then 'native bcp (nolock table)'
                                when 4 then 'character bcp (nolock table)'
                           end
      ,article           = a.name
      ,art_local_table   = object_name(a.objid)
      ,art_remote_table  = a.dest_owner + '.' + a.dest_table
      ,art_type          = case a.type
                              when 1   then '1 = log-based article'
                              when 3   then '3 = log-based article with manual filter'
                              when 5   then '5 = log-based article with manual view'
                              when 7   then '7 = log-based article with manual filter and manual view'
                              when 8   then '8 = stored procedure execution'
                              when 24  then '24 = serializable stored procedure execution'
                              when 32  then '32 = stored procedure (schema only)'
                              when 64  then '64 = view (schema only)'
                              when 128 then '128 = function (schema only)'
                           end
      ,art_status        = case when a.status & 1 = 1 then 'active' else 'inactive' end
      ,subserver         = s.srvname
      ,subdb             = s.dest_db
  from dbo.syspublications p 
          inner join dbo.sysarticles a
    on p.pubid = a.pubid 
          inner join dbo.syssubscriptions s
    on a.artid = s.artid and s.srvid > 0
 where 1 = 1
-- and p.name               = ''    -- publication name
-- and a.name               = ''    -- article name (note:article name always same with table name)
-- and object_name(a.objid) = ''    -- table name
--order by s.srvname, a.name
order by p.name



复制代码

 
复制代码