[SCRIPT] – Enable Query Store on all databases (except on those that are already enabled)

Greetings,

The script below is a simple and very effective way to enable Query Store on all the databases that don’t have it yet.

If you want to enable Query Store for any further database that will be created in the future leave the model database commented.

Without further ado, there’s the script:

EXECUTE master.sys.sp_MSforeachdb N'
IF (N''?'' NOT IN (N''tempdb'', N''master'', /*N''model'',*/ N''msdb'')
AND (N''?'' IN (
SELECT 
    d.name
 FROM sys.databases AS d
WHERE  d.is_query_store_on=0)))
BEGIN
   ALTER DATABASE [?] SET QUERY_STORE = ON;
   ALTER DATABASE [?] SET QUERY_STORE
    (OPERATION_MODE = READ_WRITE,
     INTERVAL_LENGTH_MINUTES = 30,
     MAX_STORAGE_SIZE_MB = 300,
     QUERY_CAPTURE_MODE = AUTO);
  --PRINT [?]
END;
';