“Not for Replication”
is a property which can be set for different objects like check constraints
,
Foreign Key constraints
,
Triggers
, Identity columns
etc while using SQL Server Replication.
What are ALL object types that can be NOT FOR REPLICATION?
How can I find them?
For those object types I have mentioned above I am putting a script together:
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]
triggers
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
check constraints
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
foreign key constraints
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
Please note that I have used sys.partitions and that is not a requirement for this question, but I like to have an idea of the space each object occupies and where it is physically located.
Best Answer
Row Level Security Policies have a NOT FOR REPLICATION property too, allowing the replication agents to bypass the policy. But I think that's it, at least those are the only objects with is_not_for_replciation column in the catalog.