Script to drop articles from transactional replication

--This script builds a script to drop articles from replication in
transactional publications
--It will build the necessary replication procedures and parameters to
remove articles from replication
--Instructions
--Either hard code the where clause criteria or have it look in a table you
have already populated
--Run this script on the distribution server
--Be sure to have the results output to text
--Copy the results and execute on the server where the publications exist
use distribution
go
select 'use ' + p.publisher_db + char(13) + 'go ' + char(13) + 'exec sp
dropsubscription @.publication = ''' + p.publication + ''', @.article = ''' +
a.article + ''', @.subscriber = ''' + s.srvname + ''', @.destination_db = ''' +
sub.subscriber_db + '''' + char(13) + 'exec sp_droparticle @.publication = '''
+ p.publication + ''', @.article = ''' + a.article + '''' + char(10) + char(13)
from dbo.mspublications p
inner join
dbo.msarticles a on p.publication_id = a.publication_id
inner join
dbo.msdistribution_agents ag on p.publisher_id = ag.publisher_id and
p.publisher_db = ag.publisher_db
inner join
master.dbo.sysservers s on ag.subscriber_id = s.srvid
inner join
dbo.mssubscriptions sub on ag.id = sub.agent_id and
sub.publisher_id = p.publisher_id and
sub.publisher_db = p.publisher_db and
sub.article_id = a.article_id
where a.article in(
'table1','table2','etc..'
)
and p.publisher_db = 'db_name'
--Sorry, had a typo
--This script builds a script to drop articles from replication in
transactional publications
--It will build the necessary replication procedures and parameters to
remove articles from replication
--Instructions
--Either hard code the where clause criteria or have it look in a table you
have already populated
--Run this script on the distribution server
--Be sure to have the results output to text
--Copy the results and execute on the server where the publications exist
use distribution
go
select 'use ' + p.publisher_db + char(13) + 'go ' + char(13) + 'exec
sp_dropsubscription @.publication = ''' + p.publication + ''', @.article = '''
+
a.article + ''', @.subscriber = ''' + s.srvname + ''', @.destination_db = ''' +
sub.subscriber_db + '''' + char(13) + 'exec sp_droparticle @.publication = '''
+ p.publication + ''', @.article = ''' + a.article + '''' + char(10) + char(13)
from dbo.mspublications p
inner join
dbo.msarticles a on p.publication_id = a.publication_id
inner join
dbo.msdistribution_agents ag on p.publisher_id = ag.publisher_id and
p.publisher_db = ag.publisher_db
inner join
master.dbo.sysservers s on ag.subscriber_id = s.srvid
inner join
dbo.mssubscriptions sub on ag.id = sub.agent_id and
sub.publisher_id = p.publisher_id and
sub.publisher_db = p.publisher_db and
sub.article_id = a.article_id
where a.article in(
'table1','table2','etc..'
)
and p.publisher_db = 'db_name'