Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Setting page verification to CHECKSUM
--https://www.brentozar.com/blitz/page-verification/
--An alternative to sp_msforeachdb
--https://johnmccormack.it/2015/12/an-alternative-to-sp_msforeachdb/
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
SELECT name, 0 AS completed
INTO #tmp
FROM sys.databases AS s
WHERE s.page_verify_option_desc <> 'CHECKSUM'
--database_id > 4
AND state_desc = 'ONLINE'
AND is_read_only = 0;
--and name NOT IN('','') -- A list of DBs you don't wish to include
--AND name IN('') -- A list of DBs you DO wish to include
DECLARE @dbname sysname;
DECLARE @cmdSQL NVARCHAR(4000);
WHILE EXISTS (SELECT 1 FROM #tmp WHERE completed = 0)
BEGIN
SET @dbname = ( SELECT TOP 1 name FROM #tmp WHERE completed = 0 ); -- You can ORDER BY name if you care about the order
--build dynamic SQL
SET @cmdSQL = N'ALTER DATABASE [' + @dbname + N'] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT ;';
PRINT @cmdSQL;
EXEC sp_executesql @cmdSQL;
-- ^^^^ if you want to actually execute the statement
UPDATE #tmp
SET completed = 1
WHERE name = @dbname;
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.