--This query retrieves information about the databases that have been defined on the
--server.  This is one of the most important tables in the master database.
--This query gives database names, their status, create date and its path.
--Author: Su Ge
--Date 9-8-1999

use master
go

SELECT substring(name,1,25) AS 'Database Name', Status=
	CASE
	WHEN status= 0 THEN 'Option is set to default when DB is created'
	WHEN status=1 THEN 'Autoclose'
	WHEN status=4 THEN 'Select into/bulkcopy'
	WHEN status=8 THEN 'Trunc. log on chkpt'
	WHEN status=12 THEN 'Select into/bulkcopy,Trunc.log on chkpt'
	WHEN status=16 THEN 'Torn page detection'
	WHEN status=32 THEN 'Loading'
	WHEN status=64 THEN 'Pre recovery'
	WHEN status=128 THEN 'Recovering'
	WHEN status=256 THEN 'Not recovered'
	WHEN status=512 THEN 'Offline'
	WHEN status=1024 THEN 'Read only'
	WHEN status=2048 THEN 'dbo use only '
	WHEN status=4096 THEN 'Single user'
	WHEN status=32768 THEN 'Emergency mode'
	WHEN status=4194304 THEN 'Autoshrink'
END,
crdate as 'Create Date', filename
from sysdatabases
go