sql server replication - how to get combination of values from a binary(8) field?

Question

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.

Answer 1

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

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

• gravidThoughts: This results in the following Msg 402, Level 16, State 1, Line 62 The data types binary and varbinary are incompatible in the '&' operator.