Using sp_configure

sp_configure displays and resets configuration parameters. You can restrict the number of parameters displayed by sp_configure using sp_displaylevel to set your display level to one of three values:

Basic
Intermediate
Comprehensive

For information about display levels, see “User-defined subsets of the parameter hierarchy: display levels”. For information about sp_displaylevel, see the Reference Manual: Stored Procedures.

Table 4-1 describes the syntax for sp_configure. The information in the “Effect” column assumes that your display level is set to “comprehensive.”

**Table 4-1: sp_configure syntax**
Command	Effect
sp_configure	Displays all configuration parameters by group, their current values, their default values, the value to which they have most recently been set, and the amount of memory used by this particular setting.
sp_configure “parameter”	Displays current value, default value, most recently changed value, and amount of memory used by setting for all parameters matching parameter.
sp_configure “parameter”, value	Resets parameter to value.
sp_configure “parameter”, 0, “default”	Resets parameter to its default value.
sp_configure “group_name”	Displays all configuration parameters in group_name, their current values, their default values, the values to which they were recently set, and the amount of memory used by each setting.
sp_configure “configuration file”, 0, “sub_command”, “file_name”	Sets configuration parameters from the configuration file. See “Using sp_configure with a configuration file” for descriptions of the parameters.