InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 11:11:00 GMT20http://www.sqlservercentral.com/Forums/Topic784521-5-1.aspxMat 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)[code]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.sysdatabasesSELECT 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[/code]Tue, 08 Sep 2009 14:32:08 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic784521-5-1.aspxok, what about the status column of the sp_helpdb sproc?edit: doesn't look like this returns anything for offline databases in sql 2008Tue, 08 Sep 2009 14:19:49 GMTmatt6288http://www.sqlservercentral.com/Forums/Topic784521-5-1.aspxi want to know the 'state' of the databases.Means online,offline,suspect.where this information stored?Tue, 08 Sep 2009 13:48:42 GMTcharipghttp://www.sqlservercentral.com/Forums/Topic784521-5-1.aspxFound 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.sysdatabasesTue, 08 Sep 2009 13:41:20 GMTmatt6288http://www.sqlservercentral.com/Forums/Topic784521-5-1.aspxIt 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 mode4194304 = autoshrink (ALTER DATABASE)1073741824 = cleanly shutdownMultiple bits can be ON at the same time.Tue, 08 Sep 2009 13:38:50 GMTmatt6288http://www.sqlservercentral.com/Forums/Topic784521-5-1.aspxyes i checked .there is no 'state column in sysdatabases.'Tue, 08 Sep 2009 13:34:49 GMTcharipghttp://www.sqlservercentral.com/Forums/Topic784521-5-1.aspxhave you checked sysdatabases? i don't have a sql 7 instance available. try sysdatabases or sys.databasesTue, 08 Sep 2009 13:30:29 GMTmatt6288http://www.sqlservercentral.com/Forums/Topic784521-5-1.aspxwhere is the database 'state' is stored.Means in which table or view.Tue, 08 Sep 2009 13:28:17 GMTcharipg