SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Set standard database options

By Ed Vassie,

This routine sets standardised database options. It is a common problem that database options differ from the site standard, especially in Development environments. Running this routine via a weekly SQL Agent job overcomes this problem.

To set standard options for all databases:

EXEC spSetDBOptions

To set standard options for a given database:

EXEC spSetDBOptions @dbname='Your_Database'

To generate code but make no changes, use the @process parameter. To seet debugging information, use the @debug parameter:

EXEC spSetDBOptions @process='N',@debug='Y'

The setting of individual options can be suppressed by using the @setopts, @setowner, @setsize parameters.

This routine will produce an appropriate message if an errors occurs. Use the following statement to add the message to SQL Server:

EXEC sp_addmessage 700000, 16, N'%ls'

Total article views: 2116 | Views in the last 30 days: 3
Related Articles

Database Options

Database Options


Create a Comma Delimited CSV File from Any Table/View with Debugging

Dynamically build a comma delimited CSV file using the BCP utility from any table or view in your da...


Notes on debugging Database Mail problems

I’ve had to debug database mail problems several times over the last year or so. Below is a collect...


How to handle optional parameter in SSRS with oracle

passing null value to an oracle database from SSRS, incase a parameter is set optional


stored procedure with optional parameters

creating stored procedure with optional parameters

sql server 2005    
sql server 7