How to check the page_verify value of all database in SQL SERVER.

The PAGE_VERIFY option discovers database pages which have been damaged by disk errors; normally caused by hardware failure or power outage. If SQL Server finds a page which it suspects is damaged, error number 824 will be raised. Depending on the value of the PAGE_VERIFY setting, the message will report the type of suspected condition (torn page or checksum error). Normally such errors are raised during database recovery at SQL Server startup. SQL Server will write the error to its error log as well as to the application event log and retry reading the damaged page up to four times. If any of these attempts are successful the read operation will move on to the next data page, otherwise the read operation will fail. Any time error 824 is raised, SQL Server will write a record to suspect_pages system table in msdb database, however, the pages might still be readable even if they're suspected. If the read operation does fail, you'll have to attempt fixing the database using the DBCC CHECKDB statement. If only index pages are damaged, you can usually resolve the problem by rebuilding the index

There are three kind of values that we can set for page_verify in a database. For checking the value of  page_verify we can use the below command.

Values that can be set for page_verify :-
1. Checksum
2. Torn_page_detection
3. None

Command 1 :-

SELECT NAME , page_verify_option_desc  FROM sys.databases

Output 1:-

It will list the page_verify value that is currently set for all database.

Command 2 :-

SELECT NAME  FROM sys.databases  WHERE page_verify_option_desc <> 'CHECKSUM'

Output 2:- It will list all database whose page_verify value is set to checksum.