如何獲取數據庫中不用於複製的所有對象？

“Not for Replication”是一個屬性，可以在使用 SQL Server 複製時為不同的對象（如check constraints、 Foreign Key constraints、 Triggers等）Identity columns 設置。

哪些對像類型不能用於複製？我怎樣才能找到它們？

對於我上面提到的那些對像類型，我將一個腳本放在一起：

標識列

SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
, OBJECT_NAME(p.object_id) AS [Table]
, i.name AS [Index]
, p.partition_number
, p.rows AS [Row Count]
, i.type_desc AS [Index Type]
,K.increment_value as IncrementValue
,K.last_value as LastValue
,K.seed_value as SeedValue
,k.is_nullable
,k.is_identity
,k.is_filestream
,k.is_replicated
,k.is_not_for_replication

FROM sys.partitions p

INNER JOIN sys.indexes i 
ON p.object_id = i.object_id
AND p.index_id = i.index_id

LEFT OUTER JOIN sys.identity_columns K
ON P.object_id = K.object_id

where  1=1
 AND  i.index_id < 2 -- GET ONLY THE CLUSTERED INDEXES - IF EXISTS ANY
ORDER BY [Schema], [Table], [Index] 

觸發器

SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
, OBJECT_NAME(p.object_id) AS [Table]
,t.*

FROM sys.partitions p
INNER JOIN sys.triggers t
ON p.object_id = t.parent_id

檢查約束

SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
, OBJECT_NAME(p.object_id) AS [Table]
,c.*

FROM sys.partitions p
INNER JOIN sys.tables t
       ON p.object_id = t.object_id

INNER JOIN sys.check_constraints c
       ON t.object_id = c.parent_object_id

外鍵約束

SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
, OBJECT_NAME(p.object_id) AS [Table]
,fk.*

FROM sys.partitions p
INNER JOIN sys.tables t
       ON p.object_id = t.object_id

INNER JOIN sys.foreign_keys fk
       ON t.object_id = fk.parent_object_id

請注意，我使用了 sys.partitions，這不是這個問題的要求，但我想了解每個對象佔用的空間以及它的物理位置。

行級安全策略也有一個 NOT FOR REPLICATION 屬性，允許複製代理繞過該策略。但我認為就是這樣，至少這些是目錄中唯一具有 is_not_for_replciation 列的對象。

select object_name(object_id) object_name, c.name column_name
from sys.all_columns c
where c.name like '%replication%'
order by column_name

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