-- Yaniv Etrogi 20100628
-- Get all the server configuration options that were modified from the default settings
USE master;
SET NOCOUNT ON;
IF OBJECT_ID('TEMPDB.dbo.#DefaultConfig') IS NOT NULL DROP TABLE #DefaultConfig;
CREATE TABLE #DefaultConfig ([name] sysname, minimum int, maximum int, config_value int, run_value int );
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('access check cache bucket count',0,16384,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('access check cache quota',0,2147483647,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('Ad Hoc Distributed Queries',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('affinity I/O mask',-2147483648,2147483647,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('affinity mask',-2147483648,2147483647,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('Agent XPs',0,1,1,1)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('allow updates',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('awe enabled',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('backup compression default',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('blocked process threshold (s)',0,86400,10,10)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('c2 audit mode',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('clr enabled',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('common criteria compliance enabled',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('cost threshold for parallelism',0,32767,5,5)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('cross db ownership chaining',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('cursor threshold',-1,2147483647,-1,-1)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('Database Mail XPs',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('default full-text language',0,2147483647,1033,1033)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('default language',0,9999,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('default trace enabled',0,1,1,1)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('disallow results from triggers',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('EKM provider enabled',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('filestream access level',0,2,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('fill factor (%)',0,100,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('ft crawl bandwidth (max)',0,32767,100,100)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('ft crawl bandwidth (min)',0,32767,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('ft notify bandwidth (max)',0,32767,100,100)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('ft notify bandwidth (min)',0,32767,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('index create memory (KB)',704,2147483647,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('in-doubt xact resolution',0,2,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('lightweight pooling',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('locks',5000,2147483647,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('max degree of parallelism',0,64,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('max full-text crawl range',0,256,4,4)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('max server memory (MB)',16,2147483647,2147483647,2147483647)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('max text repl size (B)',-1,2147483647,65536,65536)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('max worker threads',128,32767,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('media retention',0,365,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('min memory per query (KB)',512,2147483647,1024,1024)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('min server memory (MB)',0,2147483647,0,8)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('nested triggers',0,1,1,1)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('network packet size (B)',512,32767,4096,4096)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('Ole Automation Procedures',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('open objects',0,2147483647,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('optimize for ad hoc workloads',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('PH timeout (s)',1,3600,60,60)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('precompute rank',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('priority boost',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('query governor cost limit',0,2147483647,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('query wait (s)',-1,2147483647,-1,-1)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('recovery interval (min)',0,32767,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('remote access',0,1,1,1)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('remote admin connections',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('remote login timeout (s)',0,2147483647,20,20)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('remote proc trans',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('remote query timeout (s)',0,2147483647,600,600)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('Replication XPs',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('scan for startup procs',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('server trigger recursion',0,1,1,1)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('set working set size',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('show advanced options',0,1,1,1)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('SMO and DMO XPs',0,1,1,1)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('SQL Mail XPs',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('transform noise words',0,1,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('two digit year cutoff',1753,9999,2049,2049)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('user connections',0,32767,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('user options',0,32767,0,0)
INSERT INTO #DefaultConfig ([name],[minimum],[maximum],[config_value],[run_value])VALUES('xp_cmdshell',0,1,0,0)
IF OBJECT_ID('TEMPDB.dbo.#CurrentConfig') IS NOT NULL DROP TABLE #CurrentConfig;
CREATE TABLE #CurrentConfig ([name] sysname, minimum int, maximum int, config_value int, run_value int );
INSERT #CurrentConfig ([name], minimum, maximum, config_value, run_value) EXEC sp_configure;
SELECT
c.name, c.minimum, c.maximum, d.config_value AS Default_config_value, d.run_value AS Default_run_value, c.config_value, c.run_value
FROM #CurrentConfig c
INNER JOIN #DefaultConfig d ON d.name = c.name
WHERE c.config_value <> d.config_value OR c.run_value <> d.run_value
ORDER BY c.name;
SELECT 'USE master; EXEC sp_configure ' + '''' + c.name + '''' + ', ' + CAST(c.run_value as sysname )
+ '; RECONFIGURE WITH OVERRIDE;'
--, c.minimum, c.maximum, d.config_value AS Default_config_value, d.run_value AS Default_run_value
--, c.config_value, c.run_value
FROM #CurrentConfig c
INNER JOIN #DefaultConfig d ON d.name = c.name
WHERE c.config_value <> d.config_value OR c.run_value <> d.run_value
ORDER BY c.name;