SQL Server/T-SQL Tutorial/System Functions/sp configure

Changing SQL Server Configurations

SELECT name, value_in_use
FROM sys.configurations
WHERE name IN ("max degree of parallelism")
EXEC sp_configure "max degree of parallelism", 1
RECONFIGURE
GO
EXEC sp_configure "max server memory", 250
RECONFIGURE
GO
SELECT name, value_in_use
FROM sys.configurations
WHERE name IN ("max degree of parallelism", "max server memory (MB)")

EXEC sp_configure "allow updates", 1

2>
3>
4>    EXEC sp_configure "allow updates", 1
5>    GO
Configuration option "allow updates" changed from 0 to 1. Run the RECONFIGURE statement to install.
1>

Limiting Trigger Nesting

3>
4> USE master
5> GO
Changed database context to "master".
1> -- Disable nesting
2> EXEC sp_configure "nested triggers", 0
3> RECONFIGURE WITH OVERRIDE
4> GO
Configuration option "nested triggers" changed from 1 to 0. Run the RECONFIGURE statement to install.
1> -- Enable nesting
2> EXEC sp_configure "nested triggers", 1
3> RECONFIGURE WITH OVERRIDE
4> GO
Configuration option "nested triggers" changed from 0 to 1. Run the RECONFIGURE statement to install.
1>
2>

sp_configure [configname[,configvalue]]

Running this procedure with no parameters provides you with the current configuration. 
 
sp_configure "user connections",300 
GO