This post is inspired by a question raised by a reader in the What is New in SQL Server 2012 section where I posted a note that the system stored procedure sp_dboption is not available in SQL Server 2012 anymore. Books online says the recommended alternative is to use the ALTER DATABASE command.

Naomi asked me whether the ALTER DATABASE command provides enough options to support all the options available with sp_dboption procedure. Though I had seen most of the options that I frequently use available, I had never done a full comparison to verify that. Though I was pretty sure that there will be enough options available to replace all the previous options, I thought of doing a quick review and post back my findings.

'auto create statistics'

EXEC sp_dboption 'BR', 'auto create statistics', 'TRUE';
EXEC sp_dboption 'BR', 'auto create statistics', 'FALSE';

-- Replacement
ALTER DATABASE BR SET AUTO_CREATE_STATISTICS OFF
ALTER DATABASE BR SET AUTO_CREATE_STATISTICS ON 

'auto update statistics'

EXEC sp_dboption 'BR', 'auto update statistics', 'TRUE';
EXEC sp_dboption 'BR', 'auto update statistics', 'FALSE';

-- Replacement
ALTER DATABASE BR SET AUTO_UPDATE_STATISTICS ON 
ALTER DATABASE BR SET AUTO_UPDATE_STATISTICS OFF

'autoclose'

EXEC sp_dboption 'BR', 'autoclose', 'TRUE';
EXEC sp_dboption 'BR', 'autoclose', 'FALSE';

-- Replacement
ALTER DATABASE BR SET AUTO_CLOSE ON 
ALTER DATABASE BR SET AUTO_CLOSE OFF

'autoshrink'

EXEC sp_dboption 'BR', 'autoshrink', 'TRUE';
EXEC sp_dboption 'BR', 'autoshrink', 'FALSE';

-- Replacement
ALTER DATABASE BR SET AUTO_SHRINK ON 
ALTER DATABASE BR SET AUTO_SHRINK OFF

'ANSI null default'

EXEC sp_dboption 'BR', 'ANSI null default', 'TRUE';
EXEC sp_dboption 'BR', 'ANSI null default', 'FALSE';

-- Replacement
ALTER DATABASE BR SET ANSI_NULL_DEFAULT ON 
ALTER DATABASE BR SET ANSI_NULL_DEFAULT OFF

'ANSI nulls'

EXEC sp_dboption 'BR', 'ANSI nulls', 'TRUE';
EXEC sp_dboption 'BR', 'ANSI nulls', 'FALSE';

-- Replacement
ALTER DATABASE BR SET ANSI_NULLS ON 
ALTER DATABASE BR SET ANSI_NULLS OFF

'ANSI warnings'

EXEC sp_dboption 'BR', 'ANSI warnings', 'TRUE';
EXEC sp_dboption 'BR', 'ANSI warnings', 'FALSE';

-- Replacement
ALTER DATABASE BR SET ANSI_WARNINGS ON 
ALTER DATABASE BR SET ANSI_WARNINGS OFF

'arithabort'

EXEC sp_dboption 'BR', 'arithabort', 'TRUE';
EXEC sp_dboption 'BR', 'arithabort', 'FALSE';

-- Replacement
ALTER DATABASE BR SET ARITHABORT ON 
ALTER DATABASE BR SET ARITHABORT OFF

'concat null yields null'

EXEC sp_dboption 'BR', 'concat null yields null', 'TRUE';
EXEC sp_dboption 'BR', 'concat null yields null', 'FALSE';

-- Replacement
ALTER DATABASE BR SET CONCAT_NULL_YIELDS_NULL ON 
ALTER DATABASE BR SET CONCAT_NULL_YIELDS_NULL OFF

'cursor close on commit'

EXEC sp_dboption 'BR', 'cursor close on commit', 'TRUE';
EXEC sp_dboption 'BR', 'cursor close on commit', 'FALSE';

-- Replacement
ALTER DATABASE BR SET CURSOR_CLOSE_ON_COMMIT ON 
ALTER DATABASE BR SET CURSOR_CLOSE_ON_COMMIT OFF

'default to local cursor'

EXEC sp_dboption 'BR', 'default to local cursor', 'TRUE';
EXEC sp_dboption 'BR', 'default to local cursor', 'FALSE';

-- Replacement
ALTER DATABASE BR SET CURSOR_DEFAULT LOCAL 
ALTER DATABASE BR SET CURSOR_DEFAULT GLOBAL

'numeric roundabort'

EXEC sp_dboption 'BR', 'numeric roundabort', 'TRUE';
EXEC sp_dboption 'BR', 'numeric roundabort', 'FALSE';

-- Replacement
ALTER DATABASE BR SET NUMERIC_ROUNDABORT ON 
ALTER DATABASE BR SET NUMERIC_ROUNDABORT OFF

'quoted identifier'

EXEC sp_dboption 'BR', 'quoted identifier', 'TRUE';
EXEC sp_dboption 'BR', 'quoted identifier', 'FALSE';

-- Replacement
ALTER DATABASE BR SET QUOTED_IDENTIFIER ON 
ALTER DATABASE BR SET QUOTED_IDENTIFIER OFF

'read only'

EXEC sp_dboption 'BR', 'read only', 'TRUE';
EXEC sp_dboption 'BR', 'read only', 'FALSE';

-- Replacement
ALTER DATABASE BR SET READ_ONLY 
ALTER DATABASE BR SET READ_WRITE

'offline'

EXEC sp_dboption 'BR', 'offline', 'TRUE';
EXEC sp_dboption 'BR', 'offline', 'FALSE';

-- Replacement
ALTER DATABASE BR SET OFFLINE
ALTER DATABASE BR SET ONLINE

'recursive triggers'

EXEC sp_dboption 'BR', 'recursive triggers', 'TRUE';
EXEC sp_dboption 'BR', 'recursive triggers', 'FALSE';

-- Replacement
ALTER DATABASE BR SET RECURSIVE_TRIGGERS ON 
ALTER DATABASE BR SET RECURSIVE_TRIGGERS OFF

'single user'

EXEC sp_dboption 'BR', 'single user', 'TRUE';
EXEC sp_dboption 'BR', 'single user', 'FALSE';

-- Replacement
ALTER DATABASE BR SET SINGLE_USER
ALTER DATABASE BR SET MULTI_USER

'torn page detection'

EXEC sp_dboption 'BR', 'torn page detection', 'TRUE';
EXEC sp_dboption 'BR', 'torn page detection', 'FALSE';

-- Replacement
ALTER DATABASE BR SET TORN_PAGE_DETECTION ON 
ALTER DATABASE BR SET TORN_PAGE_DETECTION OFF

'select into/bulkcopy'

EXEC sp_dboption 'BR', 'select into/bulkcopy', 'TRUE';
EXEC sp_dboption 'BR', 'select into/bulkcopy', 'FALSE';

-- Replacement
ALTER DATABASE BR SET RECOVERY BULK_LOGGED 
ALTER DATABASE BR SET RECOVERY FULL 
-- or
ALTER DATABASE BR SET RECOVERY SIMPLE

'trunc. log on chkpt.'

EXEC sp_dboption 'BR', 'trunc. log on chkpt.', 'TRUE';
EXEC sp_dboption 'BR', 'trunc. log on chkpt.', 'FALSE';

-- Replacement
ALTER DATABASE BR SET RECOVERY SIMPLE 
ALTER DATABASE BR SET RECOVERY FULL
-- or
ALTER DATABASE BR SET RECOVERY BULK_LOGGED

'dbo use only'

EXEC sp_dboption 'BR', 'dbo use only', 'TRUE';
EXEC sp_dboption 'BR', 'dbo use only', 'FALSE';

-- Replacement
ALTER DATABASE BR SET RESTRICTED_USER
ALTER DATABASE BR SET MULTI_USER 
-- or
ALTER DATABASE BR SET SINGLE_USER

'merge publish', 'published' and 'subscribed'

I have never used these options and I could not find any documentation about the replacement for these options.

See more information about the new features and changes in SQL Server 2012 at What is new in SQL Server 2012