Menu
How do I drop a replicated table in the publication as well as the subscription? (scripts only)
I do not want to remove it from replication only, because then an unreplicated copy of the table will remain on the subscriptions. I want all instances of the table removed.
So I want a script that removes a table from a publisher, and removes the table from the publication and also drops the table in the publisher and all subscribers. Ideally it would take a parameter of the table name.
713
To delete an article from a snapshot or transactional publication. Execute sp_droparticle (Transact-SQL) to delete an article, specified by @article, from a publication, specified by @publication. Specify a value of 1 for @force_invalidate_snapshot.
To remove a replication, you must drop the subscriptions, the publications, and the distributor that is configured for the replication. You can remove the replication by running the Transact-SQL script that is generated by SQL Server Enterprise Manager or SQL Server Management Studio.
Here are the steps to follow: To drop the article from the subscriber, run sp_dropsubscription. To drop the article from the publisher, run sp_droparticle. To re-add the article to publication with the Immediate_sync option set to false.
You will need to call sp_droparticle or sp_dropmergearticle to delete the article from the publication, then generate a new snapshot.
Then call DROP table to delete the table at both publisher and subscriber(s).
This is covered in both Add Articles to and Drop Articles from Existing Publications and Delete an Article.
To post the DROP statements to subscribers from the publisher, use sp_addscriptexec.
124
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
