Dropping a table from publication without re-initialization

Dropping a table from publication without re-initialization

Dropping a table from the publication is interesting. If we think about it we would think that sp_droparticle would do for us but it is not the case. We have to drop the table subscription and then drop the article. Below are the steps

1. Drop the subscription for the table

 EXEC sp_dropsubscription 
'Publication name' , 'article to be dropped', 'subscriberame','subscriber database'

If there are more than one subscriber and you want to drop for all subscribers we can use ALL which is the default value also.

Because it is dropped only from the subscriber you will still see the table in publisher properties. It will appear till we execute the below command

EXEC sp_droparticle  @publication = 'Publication name',  @article = 'article to be dropped',
  @force_invalidate_snapshot = 1;
GO


That's it the article will be dropped from the publisher. It doesn't allow to drop the article till we drop the subscription of the article in all subscribers.

Dropping of article doesn't drop the object and if you want to drop the table altogether, then we have to use 'DROP TABLE' Command.