Microsoft® SQL Server 2012 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 sys.databases catalog view to display individual database options. As mentioned previously, sp_dboption was removed, 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 AdventureWorks2012 database:

Click here to view code image

SELECT DATABASEPROPERTYEX ...