Script to decrypt the @schema_option for a Transactional Replication Article

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