1

I am able to find if Query Store is enabled:

SELECT name, is_query_store_on from sys.databases

I am able to query a specific database to determine the Query Store Read/Write Status:

SELECT actual_state_desc from sys.database_query_store_options

How do I join the two results to have the information in one table? They do not have a common field, and I assume I could write a CROSS JOIN to match the two tables up, but am unsure how without that common field.

1 Answer 1

1

Per the docs on sys.database_query_store_options:

Returns the Query Store options for this database.

"This database" meaning the database of your current connection context, or you can fully qualify it with the database name you're interested in such as SELECT * FROM SomeDatabaseName.sys.database_query_store_options. So in other words, it's a database specific system view.

To achieve what you'd want, you'd have to manually join to every instance of that system view of each database instance you were interested in, such as the following query for example:

SELECT D.Name, D.is_query_store_on, COALESCE(A.actual_state_desc, B.actual_state_desc, C.actual_state_desc) AS actual_state_desc
FROM master.sys.databases AS D
LEFT JOIN DatabaseA.sys.database_query_store_options AS A
    ON D.database_id = DB_ID(N'DatabaseA')
LEFT JOIN DatabaseB.sys.database_query_store_options AS B
    ON D.database_id = DB_ID(N'DatabaseB')
LEFT JOIN DatabaseC.sys.database_query_store_options AS C
    ON D.database_id = DB_ID(N'DatabaseC')

The above leverages LEFT OUTER JOINs to each database instance of the sys.database_query_store_options view, from the master.sys.databases view so you get every database listed back. It joins on the ID of each database by leveraging the DB_ID() function and then COALESCE() to return the appropriate column from the correct instance of sys.database_query_store_options when the join succeeded.

Alternatively you could use dynamic SQL to automatically build out the above query for any and all databases on your server but the above query works just as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.