Generate Query Store activation commands for multiple databases

generate-query-store-activate.sql

WITH DBsize AS
(
	SELECT	D1.[name] AS DatabaseName, SUM(CAST(F1.size as bigint)) * 8192 / 1024 / 1024 AS DatabaseSizeMB
	FROM	sys.databases AS D1
	JOIN	sys.master_files AS F1
	  ON	D1.database_id = F1.database_id
	WHERE	D1.database_id > 4 AND F1.type_desc = 'ROWS'
	GROUP BY D1.[name]
)
,	SizeRules AS
(
	SELECT	DatabaseName, DatabaseSizeMB
		,	CASE
				WHEN CAST(DatabaseSizeMB * 0.1 AS bigint) < 1024
				THEN 1024
				ELSE CAST(DatabaseSizeMB * 0.1 AS bigint)
			END QuerystoreMaxSize
		,	15 AS QueryStaleDays
	FROM	DBSize
)
SELECT	DatabaseName, DatabaseSizeMB, QuerystoreMaxSize, QueryStaleDays
	,	'ALTER DATABASE [' + DatabaseName + '] SET QUERY_STORE = ON; '
		+	'ALTER DATABASE [' + DatabaseName + '] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, '
		+	'CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = ' + CAST(QueryStaleDays AS varchar) +'), '
		+	'MAX_STORAGE_SIZE_MB = ' + CAST(QuerystoreMaxSize as varchar) + ', QUERY_CAPTURE_MODE = AUTO);'
FROM	SizeRules
ORDER BY DatabaseName