Removing Replication from a SQL Database
When trying to run a database update with a third party tool, I am getting a message stating “Cannot drop <table name> because it is being used for replication.”
To try to resolve this, I deleted the subscriber to the publication, but that didn’t help. I have now gone back and deleted the publication as well. I was using transactional publication.
I want to make sure that I don’t run into this error the next time I run the tool, so I’m trying to verify that the table (or the whole database for that matter) is no longer replicated. If I run the following queries, I get no results (not an error, just no results):
SELECT * FROM sys.tables WHERE is_published =1; SELECT * FROM sys.tables WHERE is_replicated =1; SELECT * FROM sys.tables WHERE is_merge_published =1;
However, if I go to the database, and under Views -> System Views -> sys.tables -> Columns -> is_published -> it says (bit, not null).
After seeing that, I right clicked the Replication folder in SQL Server Management Studio and selected Remove Distribution and then ran the following command:
Once that was done, the tool was able to run and replication was completely removed from the server.