Turning OFF or ON Query Store for All the Database in SQL Server

----// Turning OFF Query Store for All the Databases

SELECT 'USE master;' AS Script
UNION ALL
SELECT 'ALTER DATABASE ['+name+'] SET QUERY_STORE = OFF;'
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource')
AND is_query_store_on = 1;

----// Turning ON Query Store for All the Databases

SELECT 'USE master;' AS Script
UNION ALL
SELECT 'ALTER DATABASE ['+name+'] SET QUERY_STORE = ON;'
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource')
AND is_query_store_on = 0;


----// Query Store for All the Databases For Operation Mode = Read Write

SELECT 'USE master;' AS Script
UNION ALL
SELECT 'ALTER DATABASE ['+name+'] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);'
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource')
AND is_query_store_on = 0;

----// Query Store for All the Databases For Operation Mode = Read Only

SELECT 'USE master;' AS Script
UNION ALL
SELECT 'ALTER DATABASE ['+name+'] SET QUERY_STORE = ON (OPERATION_MODE = READ_ONLY);'
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource')
AND is_query_store_on = 0;

----// Read-Only Database

-- Script to make database read only 
USE [master]
GO
ALTER DATABASE [TestDb] SET READ_ONLY WITH NO_WAIT
GO

----// Read Write Database

-- Script to make database read write
USE [master]
GO
ALTER DATABASE [TestDb] SET READ_WRITE WITH NO_WAIT

GO