How to get database information using system tables. Here is the sample with flowing fields:
database_id : Database Id dbName : Database name Status : database state like 0 DataFiles : count of data files 1 = RESTORING 2 = RECOVERING | SQL Server 2008 and later 3 = RECOVERY_PENDING | SQL Server 2008 and later 4 = SUSPECT 5 = EMERGENCY | SQL Server 2008 and later 6 = OFFLINE | SQL Server 2008 and later 7 = COPYING | Azure SQL Database Active Geo-Replication 10 = OFFLINE_SECONDARY | Azure SQL Database Active Geo-Replication Data MB : size of data files LogFiles : count of log files Log MB : Size of log files User access : multiuser or single user Recovery model : simple/ full /buld logged compatibility level : compatibility of database Creation date : date of created Last backup : last backup date fulltext : full text enable autoclose : auto close info page verify option : page verify check sum option read only : read only info autoshrink : auto shrink info auto create statistics : auto create statistics auto update statistics : update statistics standby : stand by info cleanly shutdown : shutdown info
SELECT database_id,
CONVERT(VARCHAR(25), DB.name) AS dbName,
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 & R2'
WHEN 110 THEN '100 SQL Server 2012'
WHEN 120 THEN '100 SQL Server 2014'
WHEN 130 THEN '100 SQL Server 2016'
WHEN 140 THEN '100 SQL Server 2017'
WHEN 150 THEN '100 SQL Server 2019'
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
