Know Unknown of our Databases

Some times it is necessary to know database related details, such as Database Status, DataSize, Compatability, Creation Date, Lastbackup Details.

While browsing on internet I got this query, which is useful to know – unknown of our databases.

SELECT database_id[Database ID],

CONVERT(VARCHAR(25), DB.name)AS DatabaseName,

CONVERT(VARCHAR(10),DATABASEPROPERTYEX(name,‘status’))AS [Status],

state_desc[Status Description],

(SELECTCOUNT(1)FROMsys.master_filesWHEREDB_NAME(database_id)= DB.nameAND type_desc =‘rows’)AS DataFiles,

(SELECTSUM((size*8)/1024)FROMsys.master_filesWHEREDB_NAME(database_id)= DB.nameAND type_desc =‘rows’)AS [Data MB],

(SELECTCOUNT(1)FROMsys.master_filesWHEREDB_NAME(database_id)= DB.nameAND type_desc =‘log’)AS LogFiles,

(SELECTSUM((size*8)/1024)FROMsys.master_filesWHEREDB_NAME(database_id)= DB.nameAND 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)’

ENDAS [compatibility level],

CONVERT(VARCHAR(20), create_date, 103)+‘ ‘+CONVERT(VARCHAR(20), create_date, 108)AS [Creation date],

— last backup

ISNULL((SELECTTOP 1

CASETYPEWHEN‘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)ASVARCHAR(4))+‘ ‘+‘seconds)’

FROM msdb..backupset BK WHERE BK.database_name = DB.nameORDERBY backup_set_id DESC),‘-‘)

AS [Last Backup Date],

CASEWHEN is_fulltext_enabled = 1 THEN‘Fulltext enabled’ELSEENDAS [FullText],

CASEWHEN is_auto_close_on = 1 THEN‘autoclose’ELSEENDAS [Autoclose],

page_verify_option_desc AS [page verify option],

CASEWHEN is_read_only = 1 THEN‘read only’ELSEENDAS [Read Only],

CASEWHEN is_auto_shrink_on = 1 THEN‘autoshrink’ELSEENDAS [Auto Shrink],

CASEWHEN is_auto_create_stats_on = 1 THEN‘auto create statistics’ELSEENDAS [auto create statistics],

CASEWHEN is_auto_update_stats_on = 1 THEN‘auto update statistics’ELSEENDAS [auto update statistics],

CASEWHEN is_in_standby = 1 THEN‘standby’ELSEENDAS [Standby],

CASEWHEN is_cleanly_shutdown = 1 THEN‘cleanly shutdown’ELSEENDAS [Cleanly Shutdown]

FROMsys.databases DB

ORDERBY DatabaseName