sql server 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'