How to Script SQL Server Configuration Options

To query the configuration settings of a SQL Server, run the following query:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure
GO

sp_configure_output

You can see the configuration settings of a SQL Server also by querying the sys.configurations Catalog View:

SELECT * from sys.configurations
ORDER BY name
GO

sys_configurations_output

If you want to configure a new server with all the configuration settings same as in the old server, run the following code on the old server:

SELECT 'EXEC sp_configure ''' + name + ''', ' + CAST(value AS VARCHAR(100))
FROM sys.configurations
ORDER BY name

sys_configurations_script_output

Copy the results from the old server and paste it to the new query window in the new server, and run the code:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

--PASTE THE RESULTS HERE

EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

sp_configure_new_server

Different SQL Server versions have some different configuration settings.