SQL Server Replication – How to Get Combination of Values from a Binary(8) Field?

replicationsql servertransactional-replicationvarbinary

I am using the table sysschemaarticles to collect information in my transactional replication.

It is used for a variety of things including adding new articles and finding out who are the subscriber Servers.

this script needs to be run in the publication database.

SELECT  
 PublisherDB=db_name()  
,PublisherName=sp.name
,The_Type=so.type_desc
,The_Schema=schema_name(so.schema_id)
,The_Object=sa.name
,SubscriberServerName=UPPER(srv.srvname)  
,so.is_schema_published
,so.is_published
,sa.schema_option
from dbo.syspublications sp  
INNER JOIN dbo.sysschemaarticles sa on sp.pubid = sa.pubid 
INNER JOIN sys.objects so on sa.objid = so.object_id
INNER JOIN dbo.syssubscriptions s on sa.artid = s.artid 
INNER JOIN master.dbo.sysservers srv on s.srvid = srv.srvid 

enter image description here

However the schema_option has different options that can be associated with it, and I would like to figure out ALL of them.

You can see the possible options on the picture below.

enter image description here

How can I get ALL the values stored in a binary(8) field?

I believe there could be a combination of options in this case.

Best Answer

Use the built-in bitwise operators to examine the column. For example

select
    schema_option & 0x01 AS  GeneratesObjectCreation,
    schema_option & 0x02 AS  GenerateCustomSP,
... etc.