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:

SELECT DATABASEPROPERTYEX ...

Get Microsoft SQL Server 2014 Unleashed now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.