Script to decrypt the @schema_option for a Transactional Replication Article


Chris Skorlinski
Microsoft SQL Server Escalation Services

The code below helps decrypt @schema_option settings for Transaction Replication articles. Note the meaning of binary offset may change in future builds of SQL Server. Always check Microsoft docs for most accurate listing. You’ll find scheme_options listing documented at sp_addarticle.

Code for this example now posted on https://github.com/ReplTalk/ReplScripts/blob/master/schema_option.sql

Example:

—————————-

–Set the schema_option value that you want to decrypt here

SET @schemaoption = 0x000000000803509F—Replace the value here

——————————

 

**SCHEMA OPTIONS HERE ARE**

—————————————

0x01 – Generates the object creation script (CREATE TABLE, CREATE PROCEDURE, and so on)…

0x02 – Generates the stored procedures that propagate changes for the article, if defined.

0x04 – Identity columns are scripted using the IDENTITY property.

0x08 – Replicate timestamp columns. If not set, timestamp columns are replicated as binary.

0x10 – Generates a corresponding clustered index.

0x80 – Replicates primary key constraints. Any indexes related to the constraint are also replicated…

0x1000 – Replicates column-level collation

0x4000 – Replicates UNIQUE constraints. Any indexes related to the constraint are also replicated…

0x10000 – Replicates CHECK constraints as NOT FOR REPLICATION so that the constraints are not enforced during synchronization

0x20000 – Replicates FOREIGN KEY constraints as NOT FOR REPLICATION so that the constraints are not enforced during synchronization

0x8000000 – Creates any schemas not already present on the subscriber

Comments (0)

Skip to main content