Getting the list of options in use from set_options in a query store query plan to make sure you have SSMS settings right

We know that SQL Server can cache multiple query plans for the same query based on the SET_OPTIONS for that query, and that SSMS doesn't have the same options as the standard library. (https://www.sommarskog.se/query-plan-mysteries.html). He even includes a chart! 

 But I needed to verify what options were in place on a query so that I could make sure that SSMS matched, since I'd changed settings over the years (and new SSMS, who dis?). I asked the friendly neighborhood LLM and it flat out made up options and flags, but at least it gave me a basic idea how to do the query. So run this query first in the query store to get your set_options: 

SELECT 
    q.query_id,
    q.query_text_id,
    q.object_id,
    OBJECT_NAME(q.object_id) AS [object_name],
    q.context_settings_id,
    cs.set_options, -- Binary representation of SET options
    q.query_parameterization_type_desc
FROM sys.query_store_query q
JOIN sys.query_context_settings cs 
    ON q.context_settings_id = cs.context_settings_id
WHERE q.object_id = OBJECT_ID('yourSPhere');



SELECT q.query_id, convert(bigint, cs.set_options) as set_options, 
       cs.language_id, cs.date_format, cs.date_first, cs.default_schema_id
FROM   sys.query_store_query q
JOIN   sys.query_context_settings cs 
       ON q.context_settings_id = cs.context_settings_id
WHERE  q.object_id = object_id('dbo.yourSPhere')

and then this to get your final answer:

DECLARE @set_options_value INT = 4347; -- Replace with your observed value

PRINT 'Set options for value ' + CAST(@set_options_value AS VARCHAR) + ':';
IF @set_options_value & 1 = 1 PRINT ' - ANSI_PADDING';
IF @set_options_value & 2 = 2 PRINT ' - Parallel Plans';
IF @set_options_value & 4 = 4 PRINT ' - FORCEPLAN';
IF @set_options_value & 8 = 8 PRINT ' - CONCAT_NULL_YIELDS_NULL';
IF @set_options_value & 16 = 16 PRINT ' - ANSI_WARNINGS';
IF @set_options_value & 32 = 32 PRINT ' - ANSI_NULLS';
IF @set_options_value & 64 = 64 PRINT ' - QUOTED_IDENTIFIER';
IF @set_options_value & 128 = 128 PRINT ' - ANSI_NULL_DFLT_ON'
IF @set_options_value & 256 = 256 PRINT ' - ANSI_NULL_DFLT_OFF'
IF @set_options_value & 512 = 512 PRINT ' - NoBrowseTable'
IF @set_options_value & 1024 = 1024 PRINT ' - TriggerOneRow'
IF @set_options_value & 2048 = 2048 PRINT ' - ResyncQuery'
IF @set_options_value & 4096 = 4096 PRINT ' - ARITH_ABORT'
IF @set_options_value & 8192 = 8192 PRINT ' - NUMERIC_ROUNDABORT'
IF @set_options_value & 16384 = 16384 PRINT ' - DATEFIRST'
IF @set_options_value & 32768 = 32768 PRINT ' - DATEFORMAT'
IF @set_options_value & 65536 = 65536 PRINT ' - LanguageID'
IF @set_options_value & 131072 = 131072 PRINT ' - UPON';