How to tell if a SQL Database has QUERY_STORE enabled?

Question

How can I tell if a Azure SQL Database has QUERY_STORE turned on?

You enable it with this command:

ALTER DATABASE <database_name> SET QUERY_STORE = ON;

I figure it should be simple to check the database for this, but I have not found the trick.

FYI, I tried this command on a database that had it enabled, but the command just returned null:

SELECT DATABASEPROPERTYEX ('<database_name>', 'QUERY_STORE')

Answer 1

This DMV sys.database_query_store_options should allow you to determine if QUERY_STORE is enabled:

SELECT  desired_state_desc ,
        actual_state_desc ,
        readonly_reason, 
        current_storage_size_mb , 
        max_storage_size_mb ,
        max_plans_per_query 
FROM    sys.database_query_store_options ;

Description of Actual_state_Desc states :

OFF (0)

-Not Enabled

READ_ONLY (1)

Query Store may operate in read-only mode even if read-write was specified by the user. For example, that might happen if the database is in read-only mode or if Query Store size exceeded the quota

READ_WRITE (2)

Query store is on and it is capturing all queries

ERROR (3)

Extremely rarely, Query Store can end up in ERROR state because of internal errors. In case of memory corruption, Query Store can be recovered by requesting READ_WRITE mode explicitly, using the ALTER DATABASE SET QUERY_STORE statement. In case of corruption on the disk, data must be cleared before READ_WRITE mode is requested explicitly.

Answer 2

David's answer shows if the Query store is enabled for the current database - so you would need to loop through them.

This query shows if the Query store is enabled for all databases (but doesn't show any details).

SELECT 
    d.name,
    d.is_query_store_on
FROM sys.databases AS d