Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I need to rename some tables in my DB - SQL Server 2005, with this:
EXEC sp_rename 'dbo.TableName', 'Tmp_TableName', 'OBJECT'
but I can't do this - I get this exception -
Cannot rename the table because it is published for replication
This DB doesn't have replication - it was removed 5 years ago - we've just tried it and removed it after a week. So I suppose that it should be a flag somewhere.
Please help me remove this flag somehow.
sp_removedbreplication 'DB_NAME' that will remove replication from the database.
From How to cleanup Replication Bits by Chris Skorlinski of Microsoft SQL Server Escalation Services:
sp_removedbreplication – Removes all replication objects from a database without updating data at the Distributor. This stored procedure is executed at the Publisher on the publication database or at the Subscriber, on the subscription database.
sp_removedbreplication is useful when restoring a replicated database that has no replication objects needing to be restored.
You do not need to remove the whole replication. You can just remove the single article (aka your table) from the replication. In SSMS connect to the publishing server, expand Local Publications -> Properties -> Articles. Uncheck the table you want to rename, then rename the table, then re-add it to the article list (make sure to uncheck "Show only checked articles in the list" option to see all addable objects). Then start a new snapshot in the snapshot agent: Local Publications -> Properties -> View Snapshot Agent Status -> Start.
