{"id":1734,"date":"2019-01-06T21:18:09","date_gmt":"2019-01-06T21:18:09","guid":{"rendered":"http:\/\/www.theSQLReport.com\/?p=1734"},"modified":"2019-01-06T21:18:09","modified_gmt":"2019-01-06T21:18:09","slug":"list-query-store-enabled-databases-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.theSQLReport.com\/?p=1734","title":{"rendered":"List Query Store Enabled Databases in SQL Server"},"content":{"rendered":"<p>This query will bring back only those databases where the Query Store is enabled in the SQL Server instance.<\/p>\n<pre>-- Show All Query Store Enabled Databases\r\nselect name as 'DATABASE NAME',\r\nCASE is_query_store_on when 1 then 'ENABLED'\r\nelse 'OTHER' END AS 'QUERY STORE STATE'\r\nfrom sys.databases\r\nwhere is_query_store_on = 1\r\norder by 1 ;<\/pre>\n<p><a href=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/QueryStoreEnabledDatabasesOnly.jpg\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-1739 size-full\" src=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/QueryStoreEnabledDatabasesOnly.jpg\" alt=\"Show the Query Store State for All Databases\" width=\"732\" height=\"247\" srcset=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/QueryStoreEnabledDatabasesOnly.jpg 732w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/QueryStoreEnabledDatabasesOnly-300x101.jpg 300w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/QueryStoreEnabledDatabasesOnly-210x71.jpg 210w\" sizes=\"(max-width: 732px) 100vw, 732px\" \/><\/a><\/p>\n<p>This query will show the state of the Query Store for all the databases in the SQL Server instance.<\/p>\n<pre>-- Show the Query Store State for All Databases\r\nselect name as 'DATABASE NAME',\r\nCASE is_query_store_on when 0 then 'DISABLED'\r\nwhen 1 then 'ENABLED'\r\nelse 'OTHER' END AS 'QUERY STORE STATE'\r\nfrom sys.databases\r\norder by 1 ;<\/pre>\n<p><a href=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/QueryStoreShowAllEnabledDatabases-1.jpg\" target=\"_blank\" rel=\"noopener\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-1738 size-full\" src=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2019\/01\/QueryStoreShowAllEnabledDatabases-1.jpg\" alt=\"Show the Query Store State for All Databases\" width=\"736\" height=\"316\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>This query will bring back only those databases where the Query Store is enabled in the SQL Server instance. &#8212; Show All Query Store Enabled Databases select name as &#8216;DATABASE &hellip; <a class=\"readmore\" href=\"https:\/\/www.theSQLReport.com\/?p=1734\">Continue Reading &rarr;<\/a><\/p>\n","protected":false},"author":1,"featured_media":1738,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[143],"class_list":["post-1734","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","tag-query-store"],"_links":{"self":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1734"}],"collection":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1734"}],"version-history":[{"count":4,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1734\/revisions"}],"predecessor-version":[{"id":1742,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1734\/revisions\/1742"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/1738"}],"wp:attachment":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1734"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1734"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1734"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}