Using the sysdatabases and Case statements, you can obtain a detailed report of all the databases on your server. Sysdatabases contains one row for each database. It is Compatible with SQL Server version 7 and version 2000.
SELECT LEFT(name,30) AS DatabaseName, SUBSTRING(CASE status & 1 WHEN 0 THEN '' ELSE '_,autoclose' END + CASE status & 4 WHEN 0 THEN '' ELSE ',select _into/bulk copy' END + CASE status & 8 WHEN 0 THEN '' ELSE ',trunc. _log on chkpt' END + CASE status & 16 WHEN 0 THEN '' ELSE ',torn _page detection' END + CASE status & 32 WHEN 0 THEN '' ELSE ',loading' _END + CASE status & 64 WHEN 0 THEN '' ELSE ',_pre-recovery' END + CASE status & 128 WHEN 0 THEN '' ELSE ',_recovering' END + CASE status & 256 WHEN 0 THEN '' ELSE ',_not recovered' END + CASE status & 512 WHEN 0 THEN '' ELSE ',_offline' END + CASE status & 1024 WHEN 0 THEN '' ELSE ',_read only' END + CASE status & 2048 WHEN 0 THEN '' ELSE ',_dbo USE only' END + CASE status & 4096 WHEN 0 THEN '' ELSE ',_single user' END + CASE status & 32768 WHEN 0 THEN '' ELSE ',_emergency mode' END + CASE status & 4194304 WHEN 0 THEN '' ELSE ',_autoshrink' END + CASE status & 1073741824 WHEN 0 THEN '' ELSE ',_cleanly shutdown'END + CASE status2 & 16384 WHEN 0 THEN '' ELSE ',_ANSI NULL default' END+ CASE status2 & 65536 WHEN 0 THEN '' ELSE ',_concat NULL yieldsNULL' END + CASE status2 & 131072 WHEN 0 THEN '' ELSE ',_recursive triggers'END + CASE status2 & 1048576 WHEN 0 THEN '' ELSE ',_default TO localcursor' END + CASE status2 & 8388608 WHEN 0 THEN '' ELSE ',_quoted identifier'END + CASE status2 & 33554432 WHEN 0 THEN '' ELSE ',_cursor CLOSE oncommit' END + CASE status2 & 67108864 WHEN 0 THEN '' ELSE ',_ANSI NULLs' END + CASE status2 & 268435456 WHEN 0 THEN '' ELSE ',_ANSI warnings' END+ CASE status2 & 536870912 WHEN 0 THEN '' ELSE ',_full text enabled'END,2,8000) AS DescriptionFROM master..sysdatabases
