Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Intermediate
Jan 10, 2002

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 yields
NULL' END +
        CASE status2 & 131072 WHEN 0 THEN '' ELSE ',_
recursive triggers'
END +
        CASE status2 & 1048576 WHEN 0 THEN '' ELSE ',_
default TO local
cursor' END +
        CASE status2 & 8388608 WHEN 0 THEN '' ELSE ',_
quoted identifier'
END +
        CASE status2 & 33554432 WHEN 0 THEN '' ELSE ',_
cursor CLOSE on
commit' 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 Description
FROM master..sysdatabases
Harinatha Reddy
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date