List Query Store Enabled Databases in SQL Server

This query will bring back only those databases where the Query Store is enabled in the SQL Server instance.

-- Show All Query Store Enabled Databases
select name as 'DATABASE NAME',
CASE is_query_store_on when 1 then 'ENABLED'
else 'OTHER' END AS 'QUERY STORE STATE'
from sys.databases
where is_query_store_on = 1
order by 1 ;

Show the Query Store State for All Databases

This query will show the state of the Query Store for all the databases in the SQL Server instance.

-- Show the Query Store State for All Databases
select name as 'DATABASE NAME',
CASE is_query_store_on when 0 then 'DISABLED'
when 1 then 'ENABLED'
else 'OTHER' END AS 'QUERY STORE STATE'
from sys.databases
order by 1 ;

Show the Query Store State for All Databases

Leave a Reply