Page 9 in file 1 (of the primary filegroup) in a SQL Server database is the header page and contains a wealth of metadata about the database. We can have a look at this with DBCC PAGE or DBCC DBINFO.

In order to redirect the output of the DBCC command to the results we first need to enable trace flag 3604.

DBCC TRACEON(3604)

We can then look at the header page with:

DBCC PAGE(<database name>,1,9,3)

Where 1 is the file number, 9 the page number and 3 the dump style (possible values for this parameter are 1,2 or 3).

Another option is to use this:

DBCC DBINFO(<database name>)

You can run this command without a database name parameter and it will return the header page for the current database.

You can also redirect the output in table form with the tableresults option:

DBCC DBINFO(<database name>) with tableresults

(You don’t need to run the trace flag when using tableresults).

This then naturally leads to using a table variable, loading it with the DBCC output and then having the ability to query the set.

When was the last log backup?

declare @dbinfo table
(
ParentObject varchar(255),
[Object] varchar(255),
[Field] varchar(255),
[Value] varchar(255))
insert into @dbinfo
execute('dbcc dbinfo(''<database name>'') with tableresults')
select Field, Value from @dbinfo
where Field = 'dbi_LastLogBackupTime'

When was the last clean DBCC check?

declare @dbinfo table
(
ParentObject varchar(255),
[Object] varchar(255),
[Field] varchar(255),
[Value] varchar(255))
insert into @dbinfo
execute('dbcc dbinfo(''<database name>'') with tableresults')
select Field, Value from @dbinfo
where Field = 'dbi_dbccLastKnownGood'

Caveat: DBCC PAGE and DBCC DBINFO are commonly used but they are undocumented and unsupported commands.