Cry How to...

List all databases

To get a list of all the databases available in an SQL Server instance, use either:

sp_databases

this also shows the size of each database.

A quicker alternative to just list the database names is:

select name from master..sysdatabases

if you would like the database names and the status of each database then try the following:

select name, [Status Description] =
case status & 1 when 1 then '[autoclose]' else '' end
+ case status & 4 when 4 then '[bulkcopy]' else '' end
+ case status & 8 when 8 then '[trunc. log on chkpt]' else '' end
+ case status & 16 when 16 then '[torn page detection]' else '' end
+ case status & 32 when 32 then '[loading]' else '' end
+ case status & 64 when 64 then '[pre recovery]' else '' end
+ case status & 128 when 128 then '[recovering]' else '' end
+ case status & 256 when 256 then '[not recovered]' else '' end
+ case status & 512 when 512 then '[offline]' else '' end
+ case status & 1024 when 1024 then '[read only]' else '' end
+ case status & 2048 when 2048 then '[dbo use only]' else '' end
+ case status & 4096 when 4096 then '[single user]' else '' end
+ case status & 32768 when 32768 then '[emergency mode]' else '' end
+ case status & 4194304 when 4194304 then '[autoshrink]' else '' end
+ case status & 1073741824 when 1073741824 then '[cleanly shutdown]' else '' end
from master..sysdatabases



this takes the status field, which contains the status as a series of binary flags, and converts this to a description.

These notes have been tested against SQL Server 2000.