In order to query the configuration settings of a server you can run this sproc:

exec sp_configure

On a default install of sql server this will return 16 rows. In order to see all rows you need to run this:

exec sp_configure 'show advanced options',1
reconfigure

Now when you run the sproc it returns 70 rows (SQL Server 2008 R2).

If you get curious you can have a look under the hood by running:

sp_helptext 'sp_configure'

This will return the actual code run by the sproc. Including this query:

select name,  
convert(int, minimum) as minimum,  
convert(int, maximum) as maximum,  
convert(int, isnull(value, value_in_use)) as config_value,
convert(int, value_in_use) as run_value  
from sys.configurations

This gives you all the options all the time – without having to enable the ‘show advanced options’ option.

The other cool thing about querying sys.configurations is that you get some extra columns:

select * from sys.configurations

Of most interest is the column is_dynamic. If this is a 1 then a change to the option will take effect when the reconfigure statement is run. If it is 0 then a SQL Server restart will be required.

The other useful feature is the ability to filter with a where clause. For example – are there any values that have been changed but not yet taken effect?

select * from sys.configurations where value_in_use <> value

Or you might have a subset of values that you like to check on a server:

select name,  
convert(int, minimum) as minimum,  
convert(int, maximum) as maximum,  
convert(int, isnull(value, value_in_use)) as config_value,
convert(int, value_in_use) as run_value   
from sys.configurations 
where name in (
'max degree of parallelism'
,'max server memory (MB)'
,'backup compression default'
,'remote admin connections'
)

Of course, as of SQL 2005, direct updates to system tables are not supported so sp_configure must be used to change any values.