sp_dboption used to be a very useful system procedure available till SQL 2008 R2 which helps us to identify the set options for database as well as modiying already set option values. I've used it numerous times for setting options for a db as well as retrieving set options on a database.
Starting from SQL 2012, sp_dboption has been removed. I've had some occasions recently where I had to find out set options and was frustrated on unavailability of this system sp.
I'd some research to find out an alternate way and came up with two alternatives which I'm sharing here
1. using DATABASEPROPERTYEX function
We can make use of DATABASEPROPERTYEX to get details on particular option on database
ex: To check the status of auto update statistics on a database we can use
SELECT DATABASEPROPERTYEX('Databasename','IsAutoUpdateStatistics')
Only limitation is that we need to pass in settings details as well as an argument so this can be used to return the status for only one setting at a time
2. Use catalog view sys.databases
SELECT * from sys.databases where name = 'VTest'
This will return the status of all db options for the database as bit fields along with other details for the database as shown below
Starting from SQL 2012, sp_dboption has been removed. I've had some occasions recently where I had to find out set options and was frustrated on unavailability of this system sp.
I'd some research to find out an alternate way and came up with two alternatives which I'm sharing here
1. using DATABASEPROPERTYEX function
We can make use of DATABASEPROPERTYEX to get details on particular option on database
ex: To check the status of auto update statistics on a database we can use
SELECT DATABASEPROPERTYEX('Databasename','IsAutoUpdateStatistics')
Only limitation is that we need to pass in settings details as well as an argument so this can be used to return the status for only one setting at a time
2. Use catalog view sys.databases
SELECT * from sys.databases where name = 'VTest'
This will return the status of all db options for the database as bit fields along with other details for the database as shown below
No comments:
Post a Comment