Testing SQL Server 2000: Testing Database Options

By Talmage, Ron
Published in: CODE Magazine: 2002 - July/August
Publication Date: June 1, 2002
Last updated: February 20, 2019

Published in:

Filed under:

SQL Server

Testing SQL Server 2000 applications begins with making sure your production server is configured properly.

You generally do this by verifying that the server's settings match a properly configured target computer. After configuring your server, you can go to work on validating your database configuration. This article will continue the theme of creating and implementing a Transact-SQL stored procedure to test a database's options for deviation from the default settings.

In the last issue, I highlighted SQL Server 2000 configuration settings. Given that server-level configuration settings can have important effects on SQL queries, I developed a stored procedure to test and report any deviations from the default or optimal settings.

Keep in mind that you can also set SQL Server database options. These settings are independent of a server's configuration. Some of these database-level options are equally critical as server-level configuration settings, in that some database options can have a dramatic effect on how your Transact-SQL code behaves.

In this article, you'll learn how to detect and set SQL Server 2000 database options, how to organize the multitude of database options, which options affect how SQL Server behaves, and how to test the database for deviations from the defaults or optimal settings. In this article, you will also create a stored procedure called usp_TestDbConfig.

The first step is to take a look at the various ways of determining database options and how to organize them into a coherent set of groups. You can download the full stored procedure at: http://www.code-magazine/downloads/july2002talmage.zip.

Capturing and Changing Database Settings

SQL Server 2000 provides a number of methods to capture and change a given database's settings. Some of these methods include Enterprise Manager, the system stored procedure sp_dboption, the system function DATABASEPROPERTYEX() and the ALTER DATABASE command.

Enterprise Manager

The most user-friendly way to capture and change database options is to use Enterprise Manager. By right clicking over a database name and choosing Properties, you can see many, though not all, of the settable database options with the various tabs. For example, Figure 1 shows the Options tab of the pubs database.

Figure 1: The Options tab of the Database Properties dialog shows some of the settable database options.

Note that database compatibility level is also shown. I'll explain this later.

The sp_dboption Stored Procedure

The sp_dboption stored procedure is included only for backwards compatibility, though in many contexts it is still useful. If you execute sp_dboption by itself, it returns a list of all the settable database options:

exec sp_dboption

If you pass the stored procedure a database name as a parameter, it returns the options that are set for the named database:

exec sp_dboption 'pubs'

If you add a second parameter, indicating the option you are interested in, SQL Server returns the value currently set:

exec sp_dboption 'pubs', 'ansi nulls'

Finally, you can change a setting using the stored procedure and passing the new setting as the third parameter:

exec sp_dboption 'pubs', 'ansi nulls', 'on'

While simple enough, the problem with sp_dboption is that it is obsolete because it essentially reports database options in a SQL Server 7.0 format. It does not report some newer SQL Server 2000 database options.

The sp_helpdb Stored Procedure

You can also use the sp_helpdb system stored procedure to report database options for a single database or all databases on the server. If you call the stored procedure without a parameter, such as this:

exec sp_helpdb

you will see a results set containing a list of all the databases in the system, along with the database options that have been set for that database. The options are concatenated into a string so that the options appear as a comma-delimited list. In addition, you will also see the compatibility level of the database, which we'll return to soon.

If you call sp_helpdb, passing a database name as a parameter, such as this:

exec sp_helpdb 'pubs'

you will see two results sets: one for the database and another that reports details about the files and filegroups used by the database.

The database options reported by sp_helpdb have been updated to the SQL Server 2000 set, so it has not been deprecated like sp_dboption. However, for a complete list of SQL Server 2000 database options, you need to turn to DATABASEPROPERTYEX().

Capturing SQL Server 2000 Database Options Using the DATABASEPROPERTYEX() System Function

For SQL Server 2000, the DATABASEPROPERTYEX() system function returns the most complete list of database options. For example, to find out whether the database option of ANSI_NULLS is set on for the database, you can issue:

Select DATABASEPROPERTYEX('pubs', 'IsAnsiNullsEnabled')

The DATABASEPROPERTYEX() function captures and returns individual database settings, but does not change any of them. In order to change SQL Server 2000 database options using the recommended method, you need to use the ALTER DATABASE command.

The ALTER DATABASE Command

SQL Server 2000 expands the role of the ALTER DATABASE command so that you can actually change a database's setting, as in the following example:

ALTER DATABASE pub SET ANSI_NULLS ON

You have seen the general methods for detecting and setting database options. Now, I will detail the various options themselves.

Comparing SQL Server 2000's Database Options

As you recall in SQL Server 7.0 and earlier, you could use one procedure, sp_dboption, to inspect and set database options. In SQL Server 2000, however, Microsoft recommends that you use the DATABASEPROPERTYEX() system function for detecting database option settings and the ALTER DATABASE command to set the options.

While it is a challenge initially just to comprehend the multitude of settings, the SQL Server books Online provide some relief. Table 1 shows the various database options organized somewhat along the lines suggested in the "Setting Database Options" article in Books Online. I also added entries for Collation, Full-text Search and Replication, as well as a column for values from the sp_dboption stored procedure. This way, you can compare all three methods for detecting and setting options.

Organized in this way, you can selectively focus on the database options that affect behavior of Transact SQL code and what settings should be tested. A close examination also shows some important differences between the methods. In the following analysis, I will pay particular attention to the defaults and any normative recommendations.

Auto Options

The Auto Options settings govern the default options for how a database opens and closes, as well as how it grows and shrinks. Generally in production systems, you want databases to stay open (AUTO_CLOSE OFF), not automatically shrink in size (AUTO_SHRINK OFF) and have both of the statistics options ON. These are the default settings. Note that sp_dboption also monitors the same settings.

Collation

Collation is set by the CREATE DATABASE statement and cannot be altered, so there is not an entry for it in the ALTER DATABASE command. Also, note that sp_dboption does not report it (database level collation is a new feature of SQL Server 2000). The default SQL Server 2000 collation is ' SQL_Latin1_General_CP1_CI_AS' and a database inherits this by default from the server-level collation. In testing a database's options, you will want to compare a database's collation with the default and with the server-level collation.

Cursor Options

The cursor option, CURSOR_CLOSE_ON_COMMIT, governs whether a cursor closes when a transaction is committed so that you don't need to close it with code. The default value for CURSOR_CLOSE_ON_COMMIT is OFF.

The CURSOR_DEFAULT option, which can be either LOCAL or GLOBAL, defaults to LOCAL, meaning that, by default, cursors will be local cursors. There are no parallel server-level settings for these options, and normally programmers set the defaults using the session-level SET commands or override the defaults by explicitly declaring the close behavior or scope of the cursor when defining it.

Full Text

The DATABASEPROPERTYEX() function returns a value for 'IsFulltextEnabled,' indicating whether Full Text Search has been enabled for this database. While considered a property of the database, it is not settable using ALTER DATABASE or sp_dboption. Instead, you can use Enterprise Manager to enable full-text search on a database.

SQL Options

The settings collected under SQL Options intersect with the user options defined for server-level configuration, and deserve a more extensive discussion. The settable database options, with their defaults, are:

ANSI_NULL_DEFAULT OFF
ANSI_NULLS OFF
ANSI_PADDING OFF
ANSI_WARNINGS OFF
ARITHABORT OFF
CONCAT_NULL_YIELDS_NULL OFF
QUOTED_IDENTIFIER OFF
NUMERIC_ROUNDABORT OFF
RECURSIVE_TRIGGERS OFF

These options can be set using ALTER DATABASE and with sp_dboption, as Table 1 shows. However, there is a problem here. You can set these options at the database (or server) level, but SQL Server ODBC and OLE DB drivers will override them.

For example, using the SQL Profiler utility, you can see that the ODBC driver, as used by OSQL.exe, sends the following commands with each login:

-- network protocol: LPC
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7

As you can see, the ODBC driver overrides QUOTED_IDENTIFIER, ANSI_WARNINGS, ANSI_PADDING, ANSI_NULLS and CONCAT_NULL_YIELDS_NULL. If you use SQL Profiler to monitor connections made through a linked server, which uses the OLE DB driver, you will see the initial connection making the same set of overrides. Query Analyzer also uses the ODBC driver, but has its own special behavior (see the sidebar, Query Analyzer Default Settings).

Since the ODBC and OLE DB drivers will be overriding these settings anyway for your session, it may be useful only to be concerned with the settings that remain, which are:

ARITHABORT OFF
CONCAT_NULL_YIELDS_NULL OFF
NUMERIC_ROUNDABORT OFF
RECURSIVE_TRIGGERS OFF

Even though the drivers override some of the SQL options, it is still worth noting when they are not set to the defaults.

These various settings come into play most importantly when creating indexed views. An indexed view requires that the following settings be ON:

ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIERS

In addition to these settings, NUMERIC_ROUNDABORT must be set to OFF. By default, the ODBC driver turns all but the ARITHABORT setting ON. Given the value of indexed views, it would seem worthwhile to warn the user if these settings are different at the database level.

State Options

Now you can see the difference between new and old ways of setting database options. UserAccess now has the three options, SINGLE_USER, RESTRICTED_USER and MULTI_USER. The 'single user' option in sp_dboption keeps the same meaning, but 'dbo use only' now does not completely communicate the full range that RESTRICTED_USER does; specifically, access to the database is restricted to database owners, database creator and system administrator roles.

Recovery Options

There are also some dramatic differences in meaning for recovery options: only DATABASEPROPERTYEX() and ALTER DATABASE use the new FULL, BULK_LOGGED and SIMPLE terminology for recovery models, whereas sp_dboption refers to the legacy 'trunc. log on chkpt.' and 'select into/bulkcopy.'

There is a deterministic relationship: if both 'trunc. log on chkpt.' and 'select into/bulkcopy' are ON, then the recovery model is FULL; if only 'select into/bulkcopy' is ON, then the recovery model is BULK_LOGGED; and if 'trunc. log on chkpt.' is ON, then the model is SIMPLE. The new SQL Server 2000 way of referring to recovery models is much clearer and sensible than the legacy way, and that in itself argues for use of DATABASEPROPERTYEX() over sp_dboption.

Replication

On the other hand, replication is a minor step backwards: the sp_dboption procedure also contains a flag indicating whether a database is enabled for any kind of publishing. These options are set by replication stored procedures so, even for sp_dboption, these are read-only properties and cannot be set by the stored procedure.

Version

The version number is reserved for use by Microsoft and does not really represent either a database setting or a property that you can change.

On the whole, you can see that there are a number of additional options provided by DATABASEPROPERTYEX() and that it is more useful now to use that function and ALTER DATABASE than the legacy sp_dboption. However, there is one piece missing.

The Missing Setting: Compatibility

There is one critical setting of a database that ALTER DATABASE, DATABASEPROPERTYEX() and sp_dboption overlook: database compatibility level. You can change the default compatibility of a database in SQL Server 2000, which is 80 (for SQL Server 2000, i.e., 8.0), to three other values: 60 for SQL Server 6.0 compatibility, 65 for SQL Server 6.5 and 70 for SQL Server 7.0. You still have a SQL Server 2000 database, but the syntax of acceptable queries mostly reverts to the prior version if you set a database's compatibility level backwards.

The purpose of this setting is to allow stored procedures and triggers to operate with a backwards compatibility right after an upgrade. The way you change and inspect compatibility level is normally with the system stored procedure sp_dbcmptlevel. For example, for my local copy of the pubs database, I can execute the following code:

exec sp_dbcmptlevel 'pubs'

This code returns the following text:

The current compatibility level is 80.

However, the sp_dbcmptlevel stored procedure does not return a result set, instead using RAISERROR to return the message. Consequently, you cannot use it to capture the result of a database's compatibility level in a temporary table, using an insert/exec combination, for example.

Fortunately, though, as pointed out by SQL Server MVP Steve Kass, you can use sp_helpdb. When you send no parameters, it returns a single result set listing all the databases and a number of properties including, in SQL Server 2000, compatibility level for each database. This makes it possible to capture a database's compatibility into a temporary table and then into a variable, as indicated in the following example:

Declare @ActualCmptLevel int
If Object_id('tempdb.dbo.#Compat') Is Not Null
  Drop Table #Compat
Create Table #Compat (dbname sysname, dbsize
  nvarchar(13) null, owner sysname, dbid
  smallint, created nvarchar(11), dbdesc
  nvarchar(600) null, cmptlevel tinyint)
Insert #Compat
  Exec sp_helpdb
Set @ActualCmptLevel = (Select cmptlevel
  From #compat
  Where dbname = 'pubs')
Select @ActualCmptLevel As 'Pubs CmptLevel'

You can send the database name as a parameter to sp_helpdb, but then it returns two results sets with different structures and Transact-SQL only allows one result set to be captured using the Insert/Exec combination.

Compatibility level is absolutely critical for a database. For example, user-defined functions will not execute with any compatibility level below 80 and the TOP n construct will not work when compatibility level is below 70. I once saw a very bright developer waste an hour trying to figure out why his queries were not running properly until I suggested he look at the compatibility level, which was indeed the problem.

In our case, the default compatibility level is 80, and I will report anything not equal to that.

Testing Database Options

Now it is time to put all of this together and create a stored procedure that tests a database's settings. The main strategy is to use DATABASEPROPERTYEX() to capture and test a database's settings and to report when they deviate from the defaults. You can download the stored procedure from its location at http://www.code-magazine/downloads/july2002talmage.zip. Listing 1 shows you the full set of code to test a database's settings.

This stored procedure reports any differences between the default database settings and what may be set on the database. The results are a table that shows any values that differ from the defaults. For example, in my copy of the pubs database I receive results in a grid, as shown in Table 2.

To check all of the databases on a server, you could cycle through the stored procedure using a Transact-SQL cursor, but there is an easier way. You can use the undocumented sp_MSforeachdb system stored procedure to cycle through them in the following fashion:

exec sp_MSforeachdb N'exec usp_TestDBOptions
  ''?'' '

This stored procedure and its sibling sp_MSforeachtable, though undocumented, are turning out to be widely used by Transact-SQL developers. If you direct the output of the above query to a grid in Query Analyzer it will be much easier to see the results for each database.

Conclusion

One of the most important aspects of developing and testing SQL Server 2000 applications is to make sure your databases are configured correctly. This article demonstrates a number of techniques for testing and validating your settings and further explains the meaning of these settings.

Query Analyzer Default Settings

Like the other SQL Server 2000 utilities, Query Analyzer uses the ODBC driver to connect to SQL Server and makes the same set of session-level settings. However, a Profiler trace reveals that after executing a number of commands, it returns to execute a final set of commands, which reset those original options. For instance, when I examine my Query Analyzer, I get the following as its last batch sent to SQL Server after an initial connection.

set nocount off

set arithabort on

set concat_null_yields_null on

set ansi_nulls on

set cursor_close_on_commit off

set ansi_null_dflt_on on

set implicit_transactions off

set ansi_padding on

set ansi_warnings on

set quoted_identifier on

Despite a minor difference, setting ANSI_NULL_DFLT_ON to ON, these happen to be the same settings that were sent with the initial connection. This is a subtle difference but an important one for database settings. When set to ON, the ANSI_NULL_DFLT_ON setting means that any CREATE TABLE or ALTER TABLE statements that do not specify the nullability property of a new column in a table will default to allowing NULL.

Actually, these last settings come from a customizable feature of Query Analyzer. You can override the ODBC driver's settings and have Query Analyzer send a customized set by choosing the Options dialog from the Tools menu and navigating to the Connection Properties dialog. The resulting changes are sent with each new connection as the last batch.

Why the 'EX' in DATABASEPROPERTYEX?

The function ends with 'EX' because it is meant to replace the SQL Server 7.0 DATABASEPROPERTY() function. This function, introduced in SQL Server 7.0, matches the sp_dboption stored procedure much more closely. However, with new and changed functionality in SQL Server 2000, Microsoft made enough substantial changes to the function that they decided to leave the DATABASEPROPERTY() function for SQL Server 7.0 compatibility only, and provide DATABASEPROPERTYEX() for SQL Server 2000.

Listing 1. The usp_TestDBOptions Stored Procedure

If Object_id('usp_TestDBOptions', 'P') Is Not Null
  Drop Procedure dbo.usp_TestDBOptions
Go
Create Procedure dbo.usp_TestDBOptions
@dbname nvarchar(256)
As
Set Nocount On
Declare @Options Table (OptionName nVarchar(128),
  DefValue nVarchar(128), ActualValue nVarchar(128))
Declare @ActualCmptLevel nvarchar(128)

-- Auto options
If DatabasePropertyEx(@dbname, 'IsAutoClose') <> 0
  Insert @Options Values ('IsAutoClose', '0',
  Cast(DatabasePropertyEx(@dbname, 'IsAutoClose')
  As nVarchar(128)))
If DatabasePropertyEx(@dbname, 'IsAutoCreateStatistics')
  <> 1
  Insert @Options Values ('IsAutoCreateStatistics', '1',
    Cast(DatabasePropertyEx(@dbname,
    'IsAutoCreateStatistics') As nVarchar(128)))
If DatabasePropertyEx(@dbname, 'IsAutoShrink') <> 0
  Insert @Options Values ('IsAutoShrink', '0',
  Cast(DatabasePropertyEx(@dbname, 'IsAutoShrink')
  As nVarchar(128)))
If DatabasePropertyEx(@dbname, 'IsAutoUpdateStatistics')
  <> 1
  Insert @Options Values ('IsAutoUpdateStatistics', '1',
  Cast(DatabasePropertyEx(@dbname,
  'IsAutoUpdateStatistics') As nVarchar(128)))

-- Collation options
If DatabasePropertyEx(@dbname, 'Collation') <>
  'SQL_Latin1_General_CP1_CI_AS'
  Insert @Options Values ('Collation',
  'SQL_Latin1_General_CP1_CI_AS',
  Cast(DatabasePropertyEx(@dbname, 'Collation')
  As nVarchar(128)))
If DatabasePropertyEx(@dbname, 'SQLSortOrder') <> 52
  Insert @Options Values ('SQLSortOrder', '52',
  Cast(DatabasePropertyEx(@dbname, 'SQLSortOrder')
  As nVarchar(128)))

-- Cursor
If DatabasePropertyEx(@dbname,
  'IsCloseCursorsOnCommitEnabled') <> 0
  Insert @Options Values
  ('IsCloseCursorsOnCommitEnabled', '0',
  Cast(DatabasePropertyEx(@dbname,
  'IsCloseCursorsOnCommitEnabled') As nVarchar(128)))
If DatabasePropertyEx(@dbname, 'IsLocalCursorsDefault')
  <> 0
  Insert @Options Values ('IsLocalCursorsDefault', '0',
  Cast(DatabasePropertyEx(@dbname,
  'IsLocalCursorsDefault') As nVarchar(128)))

-- Full Text
If DatabasePropertyEx(@dbname, 'IsFulltextEnabled') <> 0
  Insert @Options Values ('IsFulltextEnabled', '0',
  Cast(DatabasePropertyEx(@dbname, 'IsFulltextEnabled')
  As nVarchar(128)))

-- Database State
If DatabasePropertyEx(@dbname, 'Status') <> 'ONLINE'
  Insert @Options Values ('Status', 'ONLINE', Cast(DatabasePropertyEx(@dbname, 'Status')
  As nVarchar(128)))
If DatabasePropertyEx(@dbname, 'Updateability') <>
  'READ_WRITE'
  Insert @Options Values ('Updateability', 'READ_WRITE',
  Cast(DatabasePropertyEx(@dbname, 'Updateability')
  As nVarchar(128)))
If DatabasePropertyEx(@dbname, 'UserAccess') <>
  'MULTI_USER'
  Insert @Options Values ('UserAccess', 'MULTI_USER',
  Cast(DatabasePropertyEx(@dbname, 'UserAccess') As
  nVarchar(128)))

-- T-SQL options
If DatabasePropertyEx(@dbname, 'IsAnsiNullDefault') <> 0
  Insert @Options Values ('IsAnsiNullDefault', '0',
  Cast(DatabasePropertyEx(@dbname, 'IsAnsiNullDefault')
  As nVarchar(128)))
If DatabasePropertyEx(@dbname, 'IsAnsiNullsEnabled') <> 0
  Insert @Options Values ('IsAnsiNullsEnabled', '0',
  Cast(DatabasePropertyEx(@dbname, 'IsAnsiNullsEnabled')
  As nVarchar(128)))
If DatabasePropertyEx(@dbname, 'IsAnsiPaddingEnabled')
  <> 0
  Insert @Options Values ('IsAnsiPaddingEnabled', '0',
  Cast(DatabasePropertyEx(@dbname,
  'IsAnsiPaddingEnabled') As nVarchar(128)))
If DatabasePropertyEx(@dbname, 'IsAnsiWarningsEnabled')
  <> 0
  Insert @Options Values ('IsAnsiWarningsEnabled', '0',
  Cast(DatabasePropertyEx(@dbname,
  'IsAnsiWarningsEnabled') As nVarchar(128)))
If DatabasePropertyEx(@dbname,
  'IsArithmeticAbortEnabled') <> 0
  Insert @Options Values ('IsArithmeticAbortEnabled',
  '0', Cast(DatabasePropertyEx(@dbname,
  'IsArithmeticAbortEnabled') As nVarchar(128)))
If DatabasePropertyEx(@dbname, 'IsNullConcat') <> 0
  Insert @Options Values ('IsNullConcat', '0',
  Cast(DatabasePropertyEx(@dbname, 'IsNullConcat')
  As nVarchar(128)))
If DatabasePropertyEx(@dbname,
  'IsQuotedIdentifiersEnabled') <> 0
  Insert @Options Values ('IsQuotedIdentifiersEnabled',
  '0', Cast(DatabasePropertyEx(@dbname,
  'IsQuotedIdentifiersEnabled') As nVarchar(128)))
If DatabasePropertyEx(@dbname,
  'IsNumericRoundAbortEnabled') <> 0
  Insert @Options Values ('IsNumericRoundAbortEnabled',
  '0', Cast(DatabasePropertyEx(@dbname,
  'IsNumericRoundAbortEnabled') As nVarchar(128)))
If DatabasePropertyEx(@dbname,
  'IsRecursiveTriggersEnabled') <> 0
  Insert @Options Values ('IsRecursiveTriggersEnabled',
  '0', Cast(DatabasePropertyEx(@dbname,
  'IsRecursiveTriggersEnabled') As nVarchar(128)))

-- Recovery
If DatabasePropertyEx(@dbname, 'Recovery') <> 'SIMPLE'
  Insert @Options Values ('Recovery', 'SIMPLE',
  Cast(DatabasePropertyEx(@dbname, 'Recovery')
  As nVarchar(128)))
If DatabasePropertyEx(@dbname,
  'IsTornPageDetectionEnabled') <> 1
  Insert @Options Values ('IsTornPageDetectionEnabled',
  '1', Cast(DatabasePropertyEx(@dbname,
  'IsTornPageDetectionEnabled') As nVarchar(128)))
If DatabasePropertyEx(@dbname, 'IsInStandBy') <> 0
  Insert @Options Values ('IsInStandBy', '0',
  Cast(DatabasePropertyEx(@dbname, 'IsInStandBy')
  As nVarchar(128)))

-- Replication
If DatabasePropertyEx(@dbname, 'IsMergePublished') <> 0
  Insert @Options Values ('IsMergePublished', '0',
  Cast(DatabasePropertyEx(@dbname, 'IsMergePublished')
  As nVarchar(128)))
If DatabasePropertyEx(@dbname, 'IsSubscribed') <> 0
  Insert @Options Values ('IsSubscribed', '0',
  Cast(DatabasePropertyEx(@dbname, 'IsSubscribed')
  As nVarchar(128)))

-- Compatibility level
If Object_id('tempdb.dbo.#Compat') Is Not Null
  Drop Table #Compat
Create Table #Compat (dbname sysname
  , dbsize nvarchar(13) null
  , owner sysname
  , dbid smallint
  , created nvarchar(11)
  , dbdesc nvarchar(600) null
  , cmptlevel tinyint)
Insert #Compat
  Exec sp_helpdb
Set @ActualCmptLevel = (Select cmptlevel
  From #compat
  Where dbname = @dbname)
If @ActualCmptLevel Is Not Null
  And @ActualCmptLevel <> 80
  Insert @Options Values ('CmptLevel', '80',
    @ActualCmptLevel)

-- Return results if there are any
If Exists(Select * from @Options)
  Select * from @Options

	ALTER DATABASE	DATABASEPROPERTYEX()	sp_dboption
Auto Options	AUTO_CLOSE ON \| OFF	IsAutoClose	autoclose
	AUTO_CREATE_STATISTICS ON \| OFF	IsAutoCreateStatistics	auto create statistics
	AUTO_SHRINK ON \| OFF	IsAutoShrink	autoshrink
	AUTO_UPDATE_STATISTICS ON \| OFF	IsAutoUpdateStatistics	auto update statistics

Collation	COLLATE	Collation
		SQLSortOrder

Cursor Options	CURSOR_CLOSE_ON_COMMIT ON \| OFF	IsCloseCursorsOnCommitEnabled	cursor close on commit
	CURSOR_DEFAULT LOCAL \| GLOBAL	IsLocalCursorsDefault	default to local cursor

Full-Text		IsFulltextEnabled

SQL Options	ANSI_NULL_DEFAULT ON \| OFF	IsAnsiNullDefault	ANSI null default
	ANSI_NULLS ON \| OFF	IsAnsiNullsEnabled	ANSI nulls
	ANSI_PADDING ON \| OFF	IsAnsiPaddingEnabled	ANSI padding
	ANSI_WARNINGS ON \| OFF	IsAnsiWarningsEnabled	ANSI warnings
	ARITHABORT ON \| OFF	IsArithmeticAbortEnabled	arithabort
	CONCAT_NULL_YIELDS_NULL ON \| OFF	IsNullConcat	concat null yields null
	QUOTED_IDENTIFIER ON \| OFF	IsQuotedIdentifiersEnabled	quoted identifier
	NUMERIC_ROUNDABORT ON \| OFF	IsNumericRoundAbortEnabled	numeric roundabort
	RECURSIVE_TRIGGERS ON \| OFF	IsRecursiveTriggersEnabled	recursive triggers

State Options	SINGLE_USER \| RESTRICTED_USER \| MULTI_USER	UserAccess	single userdbo use only
	OFFLINE \| ONLINE	Status	offline
	READ_ONLY \| READ_WRITE	Updateability	read only

Recovery Options	RECOVERY FULL \| BULK_LOGGED \| SIMPLE	Recovery	trunc. log on chkpt.;select into/bulkcopy
	TORN_PAGE_DETECTION ON \| OFF	IsTornPageDetectionEnabled	torn page detection
		IsInStandby

Replication		IsMergePublished	merge publish
		IsSubscribed	subscribed
			published

Version		Version

Table 2: The result of running the stored procedure on a database

DBName	OptionName	DefValue	ActualValue
pubscopy	Recovery	SIMPLE	BULK_LOGGED
pubscopy	CmptLevel	80	70