收到錯誤“無法重命名表，因為它是為複製而發布的”，而它不是

我正在嘗試重命名表，但出現錯誤：

"cannot rename the table because it is published for replication"。

但該表不在已發表的文章中。

我懷疑它在某處被標記為已發布，因為可能有人在發布之前將其添加並刪除了它，但它在 SQL Server 中並未取消標記。但我不知道如何確認/取消確認。

我怎麼解決這個問題 ？

sp_droparticle 可能會成功。詳細資訊：https ://msdn.microsoft.com/en-us/library/ms173832(v=sql.105).aspx

但我不知道如何確認/取消確認。

要檢查您的伺服器、所有數據庫中涉及複製的所有表以及它們的訂閱者是什麼，我使用以下腳本：

--=========================================================================
-- 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

對於單個發布者數據庫，您可以使用以下腳本來找出要複製的表以及復製到的位置：

--=====================
-- 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

從複製中刪除這些文章之一是一個範例：

--========================================================================
-- 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

引用自：https://dba.stackexchange.com/questions/165206