Get a Summary Report of all SQLServer Databases Using a Single Select

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
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: