Home Forums SQL Server 7,2000 Administration How to tell what sp_dboptions are set on a database RE: How to tell what sp_dboptions are set on a database

  • April 4, 2007 at 6:45 am

    #698513

    i have this handy query stored in my snippets collection.

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!