Getting the error "Can't rename a table because it is published for replication" while it's not

Question

I'm trying to rename a table, but I'm getting the error :
"cannot rename the table because it is published for replication".
But this table is not among the published articles.
I'm suspecting it's flagged somewhere as published, because it's possible that someone added it before to publication, and dropped it but it was not unflagged in SQL Server. But I don't know how to confirm / disconfirm this.

How can I solve this problem ?

Answer 1

sp_droparticle will probably do the trick. Details: https://msdn.microsoft.com/en-us/library/ms173832(v=sql.105).aspx

Answer 2

But I don't know how to confirm / disconfirm this.

to check in your server, in all databases all the tables that are involved in replication and what are their subscribers I use the following script:

--=========================================================================
-- drop the temp table if it already exists
-- create it
--=========================================================================
BEGIN TRY DROP TABLE #tmp_replcationInfo END TRY BEGIN CATCH END CATCH


CREATE TABLE #tmp_replcationInfo ( 
PublisherDB VARCHAR(128),  
PublisherName VARCHAR(128), 
TableName VARCHAR(128), 
SubscriberServerName VARCHAR(128), 
) 

--=========================================================================
-- feed the temp table with data from all databases (publications)
--=========================================================================

EXEC sp_msforeachdb  
'use ?; 
IF DATABASEPROPERTYEX ( db_name() , ''IsPublished'' ) = 1 
insert into #tmp_replcationInfo 
select  
db_name() PublisherDB 
, sp.name as PublisherName 
, sa.name as TableName 
, UPPER(srv.srvname) as SubscriberServerName 
from dbo.syspublications sp  
join dbo.sysarticles sa on sp.pubid = sa.pubid 
join dbo.syssubscriptions s on sa.artid = s.artid 
join master.dbo.sysservers srv on s.srvid = srv.srvid 
' 


--=========================================================================
-- show all publications and their articles and subscribers
--=========================================================================
SELECT * FROM #tmp_replcationInfo 


--=========================================================================
-- get a list of articles that are part of more than one publication
--=========================================================================
;WITH radhe1 AS (
SELECT x=COUNT(*) OVER (PARTITION BY PublisherDB, TableName,SubscriberServerName)   
,PublisherDB,Publishername,TableName,SubscriberServerName  
FROM #tmp_replcationInfo 
),
radhe2 AS (

SELECT 
  x=ROW_NUMBER() OVER (PARTITION BY  PublisherDB
                                    ,TableName 
                                    ,SubscriberServerName 
                           ORDER BY PublisherDB
                                    ,TableName 
                                    ,SubscriberServerName )
 ,PublisherDB
 ,Publishername
 ,TableName
 ,SubscriberServerName  
 FROM RADHE1 
WHERE X > 1

)

-- show the articles that are in more than one publication (not good for performance)
SELECT * FROM radhe2

for a single publisher database you can use the following script to find out what tables are replicated and to where:

--=====================
-- get the tables that are used in replication
--=====================

;with radhe1 as (
select db_name() as [database],
       t.object_id,
       TableName=t.name 
 from sys.tables t
 where t.is_published = 1
 )
 , r2 as (
--=====================
-- get where the tables that are used in replication - which publications
--=====================
SELECT 
        Publication=P.name ,
        TableName = A.name ,
        DestinationTable = A.dest_table  
        --,p.*
        --,a.*

FROM
syspublications P 
INNER JOIN sysarticles A ON P.pubid = A.pubid
inner join radhe1 r1 on a.objid = r1.object_id
group by P.name, a.name, A.dest_table  

)

select * from r2

to remove one of these articles from replication here is an example:

--========================================================================
-- REMOVE TABLES FROM REPLICATION

-- the script to remove an article from the replication 
--========================================================================

USE MY_DATABASE
GO
exec sp_dropsubscription @publication = N'MY_DATABASE', @article = N'AuditDetails', @subscriber = N'all', @destination_db = N'all'
GO

exec sp_droparticle @publication = N'MY_DATABASE', @article = N'AuditDetails', @force_invalidate_snapshot = 0
GO



----------------------------------------
-- some articles are present in more than one publication
----------------------------------------

exec sp_dropsubscription @publication = N'MY_DATABASE-SA', @article = N'AuditDetails', @subscriber = N'all', @destination_db = N'all'
GO
exec sp_droparticle @publication = N'MY_DATABASE-SA', @article = N'AuditDetails', @force_invalidate_snapshot = 0
GO

GO

Answer 3

sp_removedbreplication can be execute to removed Replication settings.

However, this command did not remove all setting and we had to manually execute SQL commands below to clean-up the left over SQL replication settings.

Following SQL for remove Replication settings:

 sp_removedbreplication 'DB_PROD' 
 go