· 8 years ago · Jul 31, 2018, 10:00 PM
1--=========================================================================
2-- drop the temp table if it already exists
3-- create it
4--=========================================================================
5BEGIN TRY DROP TABLE #tmp_replcationInfo END TRY BEGIN CATCH END CATCH
6
7
8CREATE TABLE #tmp_replcationInfo (
9PublisherDB VARCHAR(128),
10PublisherName VARCHAR(128),
11TableName VARCHAR(128),
12SubscriberServerName VARCHAR(128),
13)
14
15--=========================================================================
16-- feed the temp table with data from all databases (publications)
17--=========================================================================
18
19EXEC sp_msforeachdb
20'use ?;
21IF DATABASEPROPERTYEX ( db_name() , ''IsPublished'' ) = 1
22insert into #tmp_replcationInfo
23select
24db_name() PublisherDB
25, sp.name as PublisherName
26, sa.name as TableName
27, UPPER(srv.srvname) as SubscriberServerName
28from dbo.syspublications sp
29join dbo.sysarticles sa on sp.pubid = sa.pubid
30join dbo.syssubscriptions s on sa.artid = s.artid
31join master.dbo.sysservers srv on s.srvid = srv.srvid
32'
33
34
35--=========================================================================
36-- show all publications and their articles and subscribers
37--=========================================================================
38SELECT * FROM #tmp_replcationInfo
39
40
41--=========================================================================
42-- get a list of articles that are part of more than one publication
43--=========================================================================
44;WITH radhe1 AS (
45SELECT x=COUNT(*) OVER (PARTITION BY PublisherDB, TableName,SubscriberServerName)
46,PublisherDB,Publishername,TableName,SubscriberServerName
47FROM #tmp_replcationInfo
48),
49radhe2 AS (
50
51SELECT
52 x=ROW_NUMBER() OVER (PARTITION BY PublisherDB
53 ,TableName
54 ,SubscriberServerName
55 ORDER BY PublisherDB
56 ,TableName
57 ,SubscriberServerName )
58 ,PublisherDB
59 ,Publishername
60 ,TableName
61 ,SubscriberServerName
62 FROM RADHE1
63WHERE X > 1
64
65)
66
67-- show the articles that are in more than one publication (not good for performance)
68SELECT * FROM radhe2
69
70--=====================
71-- get the tables that are used in replication
72--=====================
73
74;with radhe1 as (
75select db_name() as [database],
76 t.object_id,
77 TableName=t.name
78 from sys.tables t
79 where t.is_published = 1
80 )
81 , r2 as (
82--=====================
83-- get where the tables that are used in replication - which publications
84--=====================
85SELECT
86 Publication=P.name ,
87 TableName = A.name ,
88 DestinationTable = A.dest_table
89 --,p.*
90 --,a.*
91
92FROM
93syspublications P
94INNER JOIN sysarticles A ON P.pubid = A.pubid
95inner join radhe1 r1 on a.objid = r1.object_id
96group by P.name, a.name, A.dest_table
97
98)
99
100select * from r2
101
102--========================================================================
103-- REMOVE TABLES FROM REPLICATION
104
105-- the script to remove an article from the replication
106--========================================================================
107
108USE MY_DATABASE
109GO
110exec sp_dropsubscription @publication = N'MY_DATABASE', @article = N'AuditDetails', @subscriber = N'all', @destination_db = N'all'
111GO
112
113exec sp_droparticle @publication = N'MY_DATABASE', @article = N'AuditDetails', @force_invalidate_snapshot = 0
114GO
115
116
117
118----------------------------------------
119-- some articles are present in more than one publication
120----------------------------------------
121
122exec sp_dropsubscription @publication = N'MY_DATABASE-SA', @article = N'AuditDetails', @subscriber = N'all', @destination_db = N'all'
123GO
124exec sp_droparticle @publication = N'MY_DATABASE-SA', @article = N'AuditDetails', @force_invalidate_snapshot = 0
125GO
126
127GO
128
129sp_removedbreplication 'DB_PROD'
130 go