sp_dboption replacement for SQL2012

this information comes from URL: http://beyondrelational.com/modules/2/blogs/28/Posts/15429/replacement-for-system-stored-procedure-spdboption-in-sql-server-2012-denali.aspx

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’

1.EXEC sp_dboption 'BR', 'auto create statistics', 'TRUE';
2.EXEC sp_dboption 'BR', 'auto create statistics', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET AUTO_CREATE_STATISTICS OFF
6.ALTER DATABASE BR SET AUTO_CREATE_STATISTICS ON

‘auto update statistics’

1.EXEC sp_dboption 'BR', 'auto update statistics', 'TRUE';
2.EXEC sp_dboption 'BR', 'auto update statistics', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET AUTO_UPDATE_STATISTICS ON
6.ALTER DATABASE BR SET AUTO_UPDATE_STATISTICS OFF

‘autoclose’

1.EXEC sp_dboption 'BR', 'autoclose', 'TRUE';
2.EXEC sp_dboption 'BR', 'autoclose', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET AUTO_CLOSE ON
6.ALTER DATABASE BR SET AUTO_CLOSE OFF

‘autoshrink’

1.EXEC sp_dboption 'BR', 'autoshrink', 'TRUE';
2.EXEC sp_dboption 'BR', 'autoshrink', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET AUTO_SHRINK ON
6.ALTER DATABASE BR SET AUTO_SHRINK OFF

‘ANSI null default’

1.EXEC sp_dboption 'BR', 'ANSI null default', 'TRUE';
2.EXEC sp_dboption 'BR', 'ANSI null default', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET ANSI_NULL_DEFAULT ON
6.ALTER DATABASE BR SET ANSI_NULL_DEFAULT OFF

‘ANSI nulls’

1.EXEC sp_dboption 'BR', 'ANSI nulls', 'TRUE';
2.EXEC sp_dboption 'BR', 'ANSI nulls', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET ANSI_NULLS ON
6.ALTER DATABASE BR SET ANSI_NULLS OFF

‘ANSI warnings’

1.EXEC sp_dboption 'BR', 'ANSI warnings', 'TRUE';
2.EXEC sp_dboption 'BR', 'ANSI warnings', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET ANSI_WARNINGS ON
6.ALTER DATABASE BR SET ANSI_WARNINGS OFF

‘arithabort’

1.EXEC sp_dboption 'BR', 'arithabort', 'TRUE';
2.EXEC sp_dboption 'BR', 'arithabort', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET ARITHABORT ON
6.ALTER DATABASE BR SET ARITHABORT OFF

‘concat null yields null’

1.EXEC sp_dboption 'BR', 'concat null yields null', 'TRUE';
2.EXEC sp_dboption 'BR', 'concat null yields null', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET CONCAT_NULL_YIELDS_NULL ON
6.ALTER DATABASE BR SET CONCAT_NULL_YIELDS_NULL OFF

‘cursor close on commit’

1.EXEC sp_dboption 'BR', 'cursor close on commit', 'TRUE';
2.EXEC sp_dboption 'BR', 'cursor close on commit', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET CURSOR_CLOSE_ON_COMMIT ON
6.ALTER DATABASE BR SET CURSOR_CLOSE_ON_COMMIT OFF

‘default to local cursor’

1.EXEC sp_dboption 'BR', 'default to local cursor', 'TRUE';
2.EXEC sp_dboption 'BR', 'default to local cursor', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET CURSOR_DEFAULT LOCAL
6.ALTER DATABASE BR SET CURSOR_DEFAULT GLOBAL

‘numeric roundabort’

1.EXEC sp_dboption 'BR', 'numeric roundabort', 'TRUE';
2.EXEC sp_dboption 'BR', 'numeric roundabort', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET NUMERIC_ROUNDABORT ON
6.ALTER DATABASE BR SET NUMERIC_ROUNDABORT OFF

‘quoted identifier’

1.EXEC sp_dboption 'BR', 'quoted identifier', 'TRUE';
2.EXEC sp_dboption 'BR', 'quoted identifier', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET QUOTED_IDENTIFIER ON
6.ALTER DATABASE BR SET QUOTED_IDENTIFIER OFF

‘read only’

1.EXEC sp_dboption 'BR', 'read only', 'TRUE';
2.EXEC sp_dboption 'BR', 'read only', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET READ_ONLY
6.ALTER DATABASE BR SET READ_WRITE

‘offline’

1.EXEC sp_dboption 'BR', 'offline', 'TRUE';
2.EXEC sp_dboption 'BR', 'offline', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET OFFLINE
6.ALTER DATABASE BR SET ONLINE

‘recursive triggers’

1.EXEC sp_dboption 'BR', 'recursive triggers', 'TRUE';
2.EXEC sp_dboption 'BR', 'recursive triggers', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET RECURSIVE_TRIGGERS ON
6.ALTER DATABASE BR SET RECURSIVE_TRIGGERS OFF

‘single user’

1.EXEC sp_dboption 'BR', 'single user', 'TRUE';
2.EXEC sp_dboption 'BR', 'single user', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET SINGLE_USER
6.ALTER DATABASE BR SET MULTI_USER

‘torn page detection’

1.EXEC sp_dboption 'BR', 'torn page detection', 'TRUE';
2.EXEC sp_dboption 'BR', 'torn page detection', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET TORN_PAGE_DETECTION ON
6.ALTER DATABASE BR SET TORN_PAGE_DETECTION OFF

‘select into/bulkcopy’

1.EXEC sp_dboption 'BR', 'select into/bulkcopy', 'TRUE';
2.EXEC sp_dboption 'BR', 'select into/bulkcopy', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET RECOVERY BULK_LOGGED
6.ALTER DATABASE BR SET RECOVERY FULL
7.-- or
8.ALTER DATABASE BR SET RECOVERY SIMPLE

‘trunc. log on chkpt.’

1.EXEC sp_dboption 'BR', 'trunc. log on chkpt.', 'TRUE';
2.EXEC sp_dboption 'BR', 'trunc. log on chkpt.', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET RECOVERY SIMPLE
6.ALTER DATABASE BR SET RECOVERY FULL
7.-- or
8.ALTER DATABASE BR SET RECOVERY BULK_LOGGED

‘dbo use only’

1.EXEC sp_dboption 'BR', 'dbo use only', 'TRUE';
2.EXEC sp_dboption 'BR', 'dbo use only', 'FALSE';
3. 
4.-- Replacement
5.ALTER DATABASE BR SET RESTRICTED_USER
6.ALTER DATABASE BR SET MULTI_USER
7.-- or
8.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

 

Tell others about me!

Leave a Reply

Your email address will not be published. Required fields are marked *