how to drop a replicated table in both the publication and subscription

tsql replication sql-drop
hofnarwillie picture hofnarwillie · May 8, 2012 · Viewed 11k times · Source

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.

Answer

Brandon Williams picture Brandon Williams · May 8, 2012

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.

Related questions

List all tables that are currently published for replication MS-SQL

I need to get a list of all tables that are published for replication from MS-SQL databases. Is there a system stored procedure or a query I could run to generate such a list?

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More