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.
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.
Result (with advanced options enabled):
name minimum maximum config_value run_value ------------------------------- ...