September 8, 2009 at 1:28 pm
where is the database 'state' is stored.
Means in which table or view.
September 8, 2009 at 1:30 pm
have you checked sysdatabases? i don't have a sql 7 instance available. try sysdatabases or sys.databases
September 8, 2009 at 1:34 pm
yes i checked .there is no 'state column in sysdatabases.'
September 8, 2009 at 1:38 pm
It looks like it is the status column you are after but it is the addition of the bits. Someone has probably written a script to query this but here are the definition from BOL.
Status bits, some of which can be set by using ALTER DATABASE as noted:
1 = autoclose (ALTER DATABASE)
4 = select into/bulkcopy (ALTER DATABASE using SET RECOVERY)
8 = trunc. log on chkpt (ALTER DATABASE using SET RECOVERY)
16 = torn page detection (ALTER DATABASE)
32 = loading
64 = pre recovery
128 = recovering
256 = not recovered
512 = offline (ALTER DATABASE)
1024 = read only (ALTER DATABASE)
2048 = dbo use only (ALTER DATABASE using SET RESTRICTED_USER)
4096 = single user (ALTER DATABASE)
32768 = emergency mode
4194304 = autoshrink (ALTER DATABASE)
1073741824 = cleanly shutdown
Multiple bits can be ON at the same time.
September 8, 2009 at 1:41 pm
Found this on the site:
select name as DBNAME,
CASE WHEN (STATUS & 1) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [AUTOCLOSE_(ALTER_DATABASE)],
CASE WHEN (STATUS & 4) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SELECT_INTO/BULKCOPY_(ALTER_DATABASE_USING_SET_RECOVERY)],
CASE WHEN (STATUS & 8) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TRUNC._LOG_ON_CHKPT_(ALTER_DATABASE_USING_SET_RECOVERY)],
CASE WHEN (STATUS & 16) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TORN_PAGE_DETECTION_(ALTER_DATABASE)],
CASE WHEN (STATUS & 32) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [LOADING],
CASE WHEN (STATUS & 64) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [PRE_RECOVERY],
CASE WHEN (STATUS & 128) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [RECOVERING],
CASE WHEN (STATUS & 256) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [NOT_RECOVERED],
CASE WHEN (STATUS & 512) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [OFFLINE_(ALTER_DATABASE)],
CASE WHEN (STATUS & 1024) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [READ_ONLY_(ALTER_DATABASE)],
CASE WHEN (STATUS & 2048) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [DBO_USE_ONLY_(ALTER_DATABASE_USING_SET_RESTRICTED_USER)],
CASE WHEN (STATUS & 4096) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SINGLE_USER_(ALTER_DATABASE)],
CASE WHEN (STATUS & 32768) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [EMERGENCY_MODE],
CASE WHEN (STATUS & 4194304) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [AUTOSHRINK_(ALTER_DATABASE)],
CASE WHEN (STATUS &1073741824) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [CLEANLY_SHUTDOWN]
from master.dbo.sysdatabases
September 8, 2009 at 1:48 pm
i want to know the 'state' of the databases.
Means online,offline,suspect.
where this information stored?
September 8, 2009 at 2:19 pm
ok, what about the status column of the sp_helpdb sproc?
edit: doesn't look like this returns anything for offline databases in sql 2008
September 8, 2009 at 2:32 pm
Mat gave you the script to read the flags of the status column; that is exact where it is stored/accessible...it might not be exactly what you wanted, but that's where it is.
here's the both status and status2 for every db;
In 2005, the info is a little easier to read in sys.databases (select * from sys.databases)
select name as DBNAME,STATUS,
CASE WHEN (STATUS & 1) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [AUTOCLOSE_(ALTER_DATABASE)],
CASE WHEN (STATUS & 4) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SELECT_INTO/BULKCOPY_(ALTER_DATABASE_USING_SET_RECOVERY)],
CASE WHEN (STATUS & 8) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TRUNC._LOG_ON_CHKPT_(ALTER_DATABASE_USING_SET_RECOVERY)],
CASE WHEN (STATUS & 16) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [TORN_PAGE_DETECTION_(ALTER_DATABASE)],
CASE WHEN (STATUS & 32) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [LOADING],
CASE WHEN (STATUS & 64) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [PRE_RECOVERY],
CASE WHEN (STATUS & 128) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [RECOVERING],
CASE WHEN (STATUS & 256) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [NOT_RECOVERED],
CASE WHEN (STATUS & 512) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [OFFLINE_(ALTER_DATABASE)],
CASE WHEN (STATUS & 1024) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [READ_ONLY_(ALTER_DATABASE)],
CASE WHEN (STATUS & 2048) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [DBO_USE_ONLY_(ALTER_DATABASE_USING_SET_RESTRICTED_USER)],
CASE WHEN (STATUS & 4096) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [SINGLE_USER_(ALTER_DATABASE)],
CASE WHEN (STATUS & 32768) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [EMERGENCY_MODE],
CASE WHEN (STATUS & 4194304) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [AUTOSHRINK_(ALTER_DATABASE)],
CASE WHEN (STATUS &1073741824) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [CLEANLY_SHUTDOWN]
from master.dbo.sysdatabases
SELECT name as DBNAME,STATUS2,
CASE WHEN (STATUS2 & 16384) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [ANSI_null_default_(ALTER_DATABASE)],
CASE WHEN (STATUS2 & 65536) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [concat_null_yields_null_(ALTER_DATABASE)],
CASE WHEN (STATUS2 & 131072) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [recursive_triggers_(ALTER_DATABASE)],
CASE WHEN (STATUS2 & 1048576) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [default_to_local_cursor_(ALTER_DATABASE)],
CASE WHEN (STATUS2 & 8388608) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [quoted_identifier_(ALTER_DATABASE)],
CASE WHEN (STATUS2 & 33554432) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [cursor_close_on_commit_(ALTER_DATABASE)],
CASE WHEN (STATUS2 & 67108864) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [ANSI_nulls_(ALTER_DATABASE)],
CASE WHEN (STATUS2 & 268435456) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [ANSI_warnings_(ALTER_DATABASE)],
CASE WHEN (STATUS2 & 536870912) = 0 THEN 'FALSE' ELSE 'TRUE' END AS [full_text_enabled_(set_by_using_sp_fulltext_database)]
from master.dbo.sysdatabases
Lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply