Configuration Options
Because so many similar configuration options are controlled by different commands and at different levels (server, database, connection), this section organizes the configuration options by topic, rather than by command or level.
Displaying the Advanced Options
As with many operations, displaying advanced options can be achieved using several methods. One method is to query the sys.configurations catalog view as shown here:
SELECT name, minimum, maximum, value, value_in_use FROM sys.configurations WHERE is_advanced = 1 ORDER BY name;
The preceding example displays all the SQL Server advanced options. To display all the options, comment out the WHERE clause. Another method to display the advanced options is to turn on the show advanced options configuration using the following code:
EXEC sp_configure ‘show advanced options', 1; RECONFIGURE;
After you set the advanced option display on, you can use the sp_configure command to display a list of all the options.
EXEC sp_configure;
Result (with advanced options enabled):
name minimum maximum config_value run_value ------------------------------- ...
Get Microsoft SQL Server 2012 Bible 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.