SQL SERVER – Get All the Information of Database using sys.databases

Earlier I wrote blog article SQL SERVER – Finding Last Backup Time for All Database. In the response of this article I have received very interesting script from SQL Server Expert Matteo as a comment in the blog. He has written script using sys.databases which provides plenty of the information about database.

I suggest you can run this on your database and know unknown of your databases as well.

SELECT database_id,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],
state_desc,
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],
user_access_desc AS [User access],
recovery_model_desc AS [Recovery model],
CASE compatibility_level
WHEN 60 THEN '60 (SQL Server 6.0)'
WHEN 65 THEN '65 (SQL Server 6.5)'
WHEN 70 THEN '70 (SQL Server 7.0)'
WHEN 80 THEN '80 (SQL Server 2000)'
WHEN 90 THEN '90 (SQL Server 2005)'
WHEN 100 THEN '100 (SQL Server 2008)'
END AS [compatibility level],
CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
-- last backup
ISNULL((SELECT TOP 1
CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +
CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +
' (' + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'seconds)'
FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup],
CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext],
CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose],
page_verify_option_desc AS [page verify option],
CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only],
CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],
CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics],
CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics],
CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],
CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [cleanly shutdown] FROM sys.databases DB
ORDER BY dbName, [Last backup] DESC, NAME

Please let me know if you find this information useful.

Watch a 60 second video on this subject

Useful Columns in sys.databases I Check First

This catalog view has a lot of columns, and it is easy to get lost. These are the ones I look at first when I meet a new server:

• state_desc and user_access_desc, to see if a database is online and who can get in.
• recovery_model_desc, because full recovery without log backups means a growing log.
• log_reuse_wait_desc, which tells you why the log cannot be reused right now.
• compatibility_level, which often stays low after an upgrade because nobody changed it.
• page_verify_option_desc, which should normally be CHECKSUM.
• is_auto_shrink_on and is_auto_close_on, which I usually want turned off.
• create_date for tempdb, which quietly tells you when the instance last started.

Two more columns are worth a glance. collation_name shows databases that differ from the server, which can cause collation conflicts with temp tables. is_read_committed_snapshot_on tells you whether readers use row versions, which changes how readers and writers block each other.

The view shows one row per database for the whole instance, including master, model, msdb and tempdb. Filter on database_id > 4 when you want to skip those four system databases.

A common mistake is looking here for file sizes. Sizes live in sys.master_files, where the size column counts 8 KB pages, so multiply by 8 and divide by 1024 to get megabytes.

My quick health check is one query that lists every database with auto shrink on, auto close on or page verify not set to CHECKSUM. Anything that shows up gets a second look. Run it on each server once, and you will almost always learn something new about your own databases.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.