How to identiy which options were used for the "schema_option" parameter in merge replication
When an article is first added to an existing merge publication in SQL Server 2008 some options for the initial snapshot have to be specified (options such as: do we replicate constraints? do we replicate defaults? etc). These options are set in the sp_addmergearticle stored procedure, parameter = schema_option (https://msdn.microsoft.com/en-us/library/ms174329.aspx)
The value for the "schema_option" parameter is a bitwise OR of one or more values (each value is described in the article above). The sp_helpmergearticle stored procedure (https://msdn.microsoft.com/en-us/library/ms174278.aspx) shows the final value of "schema_option".
In order to determine what were the options used to generate this value, we can use the following script:
-----------SCRIPT EXEMPLE---------------
-----*********************Merge Replication**********
DECLARE @SchemaOption binary(8)
DECLARE @intermediate binary(8)
DECLARE @OptionsInText varchar(8000)
SET @OptionsInText = ' **SCHEMA OPTIONS** '
SET @OptionsInText = @OptionsInText + char(13) +
'---------------------------------------'
----------------------------
--Set the schema_option value that you want to decrypt here
SET @schemaoption = 0x00 --Replace the value here !!!!!
------------------------------
SET NOCOUNT ON
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x00 as binary(8))
IF @intermediate = 0x00
SET @optionsinText = @optionsinText + char(13) + '0x00 - Disables scripting by the Snapshot Agent and uses the provided schema precreation script defined in creation_script.'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x01 as binary(8))
IF @intermediate = 0x0000000000000001
SET @optionsinText = @optionsinText + char(13) + '0x01 - Generates the object creation (CREATE TABLE, CREATE PROCEDURE, and so on). This is the default value for stored procedure articles.'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x10 as binary(8))
IF @intermediate = 0x0000000000000010
SET @optionsinText = @optionsinText + char(13) + '0x10 - Generates a corresponding clustered index. Even if this option is not set, indexes related to primary keys and UNIQUE constraints are generated if they are already defined on a published table'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x20 as binary(8))
IF @intermediate = 0x0000000000000020
SET @optionsinText = @optionsinText + char(13) + '0x20 - Converts user-defined data types (UDT) to base data types at the Subscriber. This option cannot be used if there is a CHECK or DEFAULT constraint on a UDT column, if a UDT column is part of the primary key, or if a computed column references a UDT column'
SET @intermediate = cast(cast(@schemaoption as bigint) & 0x40 as binary(8))
IF @intermediate = 0x0000000000000040
SET @optionsinText = @optionsinText + char(13) + '0x40 - Generates corresponding nonclustered indexes. Even if this option is not set, indexes related to primary keys and UNIQUE constraints are generated if they are already defined on a published table'
SET @intermediate = cast(cast(@schemaoption as bigint) & 0x80 as binary(8))
IF @intermediate = 0x0000000000000080
SET @optionsinText = @optionsinText + char(13) + '0x80 - Replicates PRIMARY KEY constraints. Any indexes related to the constraint are also replicated, even if options 0x10 and 0x40 are not enabled'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x100 as binary(8))
IF @intermediate = 0x0000000000000100
SET @optionsinText = @optionsinText + char(13) + '0x100 - Replicates user triggers on a table article, if defined'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x200 as binary(8))
IF @intermediate = 0x0000000000000200
SET @optionsinText = @optionsinText + char(13) + '0x200 - Replicates FOREIGN KEY constraints. If the referenced table is not part of a publication, all FOREIGN KEY constraints on a published table are not replicated'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x400 as binary(8))
IF @intermediate = 0x0000000000000400
SET @optionsinText = @optionsinText + char(13) + '0x400 - Replicates CHECK constraints'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x800 as binary(8))
IF @intermediate = 0x0000000000000800
SET @optionsinText = @optionsinText + char(13) + '0x800 - Replicates defaults'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x1000 as binary(8))
IF @intermediate = 0x0000000000001000
SET @optionsinText = @optionsinText + char(13) + '0x1000 - Replicates column-level collation'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x2000 as binary(8))
IF @intermediate = 0x0000000000002000
SET @optionsinText = @optionsinText + char(13) + '0x2000 - Replicates extended properties associated with the published article source object'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x4000 as binary(8))
IF @intermediate = 0x0000000000004000
SET @optionsinText = @optionsinText + char(13) + '0x4000 - Replicates UNIQUE constraints. Any indexes related to the constraint are also replicated, even if options 0x10 and 0x40 are not enabled'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x8000 as binary(8))
IF @intermediate = 0x0000000000008000
SET @optionsinText = @optionsinText + char(13) + '0x8000 - This option is not valid for SQL Server 2005 Publishers'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x10000 as binary(8))
IF @intermediate = 0x0000000000010000
SET @optionsinText = @optionsinText + char(13) + '0x10000 - Replicates CHECK constraints as NOT FOR REPLICATION so that the constraints are not enforced during synchronization'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x20000 as binary(8))
IF @intermediate = 0x0000000000020000
SET @optionsinText = @optionsinText + char(13) + '0x20000 - Replicates FOREIGN KEY constraints as NOT FOR REPLICATION so that the constraints are not enforced during synchronization'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x40000 as binary(8))
IF @intermediate = 0x0000000000040000
SET @optionsinText = @optionsinText + char(13) + '0x40000 - Replicates filegroups associated with a partitioned table or index'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x80000 as binary(8))
IF @intermediate = 0x0000000000080000
SET @optionsinText = @optionsinText + char(13) + '0x80000 - Replicates the partition scheme for a partitioned table'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x100000 as binary(8))
IF @intermediate = 0x0000000000100000
SET @optionsinText = @optionsinText + char(13) + '0x100000 - Replicates the partition scheme for a partitioned index'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x200000 as binary(8))
IF @intermediate = 0x0000000000200000
SET @optionsinText = @optionsinText + char(13) + '0x200000 - Replicates table statistics'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x400000 as binary(8))
IF @intermediate = 0x0000000000400000
SET @optionsinText = @optionsinText + char(13) + '0x400000 - Replicates default Bindings'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x800000 as binary(8))
IF @intermediate = 0x0000000000800000
SET @optionsinText = @optionsinText + char(13) + '0x800000 - Replicates rule Bindings'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x1000000 as binary(8))
IF @intermediate = 0x0000000001000000
SET @optionsinText = @optionsinText + char(13) + '0x1000000 - Replicates the full-text index'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x2000000 as binary(8))
IF @intermediate = 0x0000000002000000
SET @optionsinText = @optionsinText + char(13) + '0x2000000 - XML schema collections bound to xml columns are not replicated'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x4000000 as binary(8))
IF @intermediate = 0x0000000004000000
SET @optionsinText = @optionsinText + char(13) + '0x4000000 - Replicates indexes on xml columns'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x8000000 as binary(8))
IF @intermediate = 0x0000000008000000
SET @optionsinText = @optionsinText + char(13) + '0x8000000 - Creates any schemas not already present on the subscriber'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x10000000 as binary(8))
IF @intermediate = 0x0000000010000000
SET @optionsinText = @optionsinText + char(13) + '0x10000000 - Converts xml columns to ntext on the Subscriber'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x20000000 as binary(8))
IF @intermediate = 0x0000000020000000
SET @optionsinText = @optionsinText + char(13) + '0x20000000 - Converts large object data types introduced in SQL Server 2005 to data types supported on earlier versions of Microsoft SQL Server'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x40000000 as binary(8))
IF @intermediate = 0x0000000040000000
SET @optionsinText = @optionsinText + char(13) + '0x40000000 - Replicates permissions'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x80000000 as binary(8))
IF @intermediate = 0x0000000080000000
SET @optionsinText = @optionsinText + char(13) + '0x80000000 - Attempts to drop dependencies to any objects that are not part of the publication'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x100000000 as binary(8))
IF @intermediate = 0x0000000100000000
SET @optionsinText = @optionsinText + char(13) + '0x100000000 - Use this option to replicate the FILESTREAM attribute if it is specified on varbinary(max) columns. Do not specify this option if you are replicating tables to SQL Server 2005 Subscribers. Replicating tables that have FILESTREAM columns to SQL Server 2000 Subscribers is not supported, regardless of how this schema option is set. See related option 0x800000000.'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x200000000 as binary(8))
IF @intermediate = 0x0000000200000000
SET @optionsinText = @optionsinText + char(13) + '0x200000000 - Converts date and time data types (date, time, datetimeoffset, and datetime2) introduced in SQL Server 2008 to data types that are supported on earlier versions of SQL Server. For information about how these types are mapped, see the "Mapping New Data Types for Earlier Versions" section in Using Multiple Versions of SQL Server in a Replication Topology.'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x400000000 as binary(8))
IF @intermediate = 0x0000000400000000
SET @optionsinText = @optionsinText + char(13) + '0x400000000 - Replicates the compression option for data and indexes. For more information, see Creating Compressed Tables and Indexes.'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x800000000 as binary(8))
IF @intermediate = 0x0000000800000000
SET @optionsinText = @optionsinText + char(13) + '0x800000000 - Set this option to store FILESTREAM data on its own filegroup at the Subscriber. If this option is not set, FILESTREAM data is stored on the default filegroup. Replication does not create filegroups; therefore, if you set this option, you must create the filegroup before you apply the snapshot at the Subscriber. For more information about how to create objects before you apply the snapshot, see Executing Scripts Before and After the Snapshot Is Applied. See related option 0x100000000.'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x1000000000 as binary(8))
IF @intermediate = 0x0000001000000000
SET @optionsinText = @optionsinText + char(13) + '0x1000000000 - Converts common language runtime (CLR) user-defined types (UDTs) to varbinary(max) so that columns of type UDT can be replicated to Subscribers that are running SQL Server 2005.'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x2000000000 as binary(8))
IF @intermediate = 0x00000002000000000
SET @optionsinText = @optionsinText + char(13) + '0x2000000000 - Converts the hierarchyid data type to varbinary(max) so that columns of type hierarchyid can be replicated to Subscribers that are running SQL Server 2005. For more information about how to use hierarchyid columns in replicated tables, see hierarchyid (Transact-SQL).'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x4000000000 as binary(8))
IF @intermediate = 0x0000004000000000
SET @optionsinText = @optionsinText + char(13) + '0x4000000000 - Replicates any filtered indexes on the table. For more information about filtered indexes, see Filtered Index Design Guidelines.'
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x8000000000 as binary(8))
IF @intermediate = 0x0000008000000000
SET @optionsinText = @optionsinText + char(13) + '0x8000000000 - Converts the geography and geometry data types to varbinary(max) so that columns of these types can be replicated to Subscribers that are running SQL Server 2005. '
SET @intermediate= cast(cast(@schemaoption as bigint) & 0x10000000000 as binary(8))
IF @intermediate = 0x0000010000000000
SET @optionsinText = @optionsinText + char(13) + '0x10000000000 - Replicates indexes on columns of type geography and geometry'
print @optionsinText
----END OF SCRIPT EXEMPLE------------
Please be advised that the code example above is just an example and it is provided "as is", with no warranties.