use [distribution]
go
select
'
use ['+publisher_db+']
exec sp_dropsubscription @publication = N'''+publication+''', @article = N'''+article+'''
, @subscriber = N''all'', @destination_db = N''all''
exec sp_droparticle @publication = N'''+publication+''', @article = N'''+source_object+''', @force_invalidate_snapshot = 1
' as dropscripts,
'
use ['+publisher_db+']
exec sp_addarticle @publication = N'''+publication+''', @article = N'''+article+''',
@source_owner = N'''+source_owner+''', @source_object = N'''+source_object+''', @type = N''logbased'',
@description = N'''', @creation_script = N'''', @pre_creation_cmd = N''drop'', @schema_option = 0x000000000803509F,
@identityrangemanagementoption = N''none'', @destination_table = N'''+destination_object+'''
, @destination_owner = N'''+isnull(destination_owner,source_owner)+''', @status = 24,
@vertical_partition = N''false'', @ins_cmd = N''CALL [dbo].[sp_MSins_dbo'+source_object+']''
, @del_cmd = N''CALL [dbo].[sp_MSdel_dbo'+source_object+']'',
@upd_cmd = N''SCALL [dbo].[sp_MSupd_dbo'+source_object+']''
' as addscripts
from
(
SELECT a.article,a.publisher_db,b.publication,a.source_owner,a.source_object,a.destination_object,a.destination_owner
FROM [distribution].[dbo].[MSarticles] a inner join
[distribution].[dbo].[MSpublications] b
on a.publication_id=b.publication_id
) aa