Microsoft® SQL Server 2008 R2 Unleashed

Retrieving Option Information

You can retrieve database settings by using several different methods. You can use the Database Properties dialog in SSMS (as described in the preceding section) to display commonly accessed options. You can also use the DATABASEPROPERTYEX function or the sp_dboption system stored procedure to display individual database options. As mentioned previously, sp_dboption is slated for removal in a future release, so the DATABASEPROPERTYEX function is preferred. This function accepts input values for the database name and the option for which you want to retrieve the value. The following is an example of a SELECT statement you can use to retrieve the Auto Shrink option for the AdventureWorks2008 database:

SELECT DATABASEPROPERTYEX ...