Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to tell what sp_dboptions are set on a database Expand / Collapse
Author
Message
Posted Tuesday, April 3, 2007 3:49 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 4:29 PM
Points: 413, Visits: 583

HI. How can I tell what sp_dboptions are set on a database? I was trying to find out if the 'truncate log on checkpoint' was set to true for a database.

Thanks,

Juanita

  




Post #355769
Posted Tuesday, April 3, 2007 4:30 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 12:12 PM
Points: 3,248, Visits: 569
Just run sp_dboptions 'databasename'. Since you aren't setting anything, it will return all the current settings.

If you add the second parameter, it will return the setting for just that one.

Refer to the BOL (sp_dboption) for more information.

-SQLBill



Post #355776
Posted Wednesday, April 4, 2007 6:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:04 AM
Points: 12,956, Visits: 32,489

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #355888
Posted Wednesday, April 4, 2007 9:48 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 4:29 PM
Points: 413, Visits: 583

Thanks so much!

Juanita




Post #355973
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse