Kolumna status w sys.databases zawiera pewne informacje o bazie danych. Ich odczyt jest trochę skomplikowany. Oto rozwiązanie z http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130275
DECLARE @Status TABLE
(
Code INT,
Msg VARCHAR(1000)
)
INSERT @Status
SELECT 1, 'autoclose (ALTER DATABASE)’ UNION ALL
SELECT 4, 'select into/bulkcopy (ALTER DATABASE using SET RECOVERY)’ UNION ALL
SELECT 8, 'trunc. log on chkpt (ALTER DATABASE using SET RECOVERY)’ UNION ALL
SELECT 16, 'torn page detection (ALTER DATABASE)’ UNION ALL
SELECT 32, 'loading’ UNION ALL
SELECT 64, 'pre recovery’ UNION ALL
SELECT 128, 'recovering’ UNION ALL
SELECT 256, 'not recovered’ UNION ALL
SELECT 512, 'offline (ALTER DATABASE)’ UNION ALL
SELECT 1024, 'read only (ALTER DATABASE)’ UNION ALL
SELECT 2048, 'dbo use only (ALTER DATABASE using SET RESTRICTED_USER)’ UNION ALL
SELECT 4096, 'single user (ALTER DATABASE)’ UNION ALL
SELECT 32768, ’emergency mode’ UNION ALL
SELECT 4194304, 'autoshrink (ALTER DATABASE)’ UNION ALL
SELECT 1073741824, 'cleanly shutdown’
SELECT d.Name,
d.dbID,
s.Msg
FROM master.dbo.sysdatabases AS d
INNER JOIN @Status AS S ON s.Code & d.status > 0
ORDER BY d.Name,
s.Code
or the other:
select
convert(varchar(20),databasepropertyex(a.name, 'Status’)) as DB_Status,
a.name
from
master.dbo.sysdatabases a
order by
a.name
