SQL Server - Query all non-default configurations

If you're a SQL Server DBA, then you know sp_configure. If not, well... it's time to learn. sp_configure is a system stored procedure which allows you to view or change SQL Server configuration settings. Min/max memory, backup compression, fill factor (%), etc. There are many different opinions on which configurations should be changed to what, but that's not what this post is about. I just want to show you how to quickly list all non-default SQL Server configuration settings. Why? Well, maybe you just inherited a server, or maybe you're troubleshooting a problem and want to see if any configurations could be contributing -- or any that could help! This returns ONLY the non-defaults. Much easier than running 'EXEC sp_configure' and then going through all configurations...

      /* Query SQL Server for any non-default configuration settings.    */
      SET NOCOUNT ON;

-- declarations
DECLARE
@version VARCHAR(128),
@charindex BIGINT,
@majversion VARCHAR(MAX),
@temp SQL_VARIANT;

-- set local variables
SELECT @version = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(128));
SELECT @charindex = CHARINDEX('.', @version);
SET @majversion = SUBSTRING(@version, 1, @charindex-1);
IF @majversion IN (9,10)
       SET @temp = 20  ELSE SET @temp = 10

-- load all defaults into table variable
DECLARE @defaults TABLE (
id int IDENTITY(1,1),
config VARCHAR(128),
value SQL_VARIANT
);

INSERT @defaults VALUES
('access check cache bucket count', 0),
('access check cache quota', 0),
('Ad Hoc Distributed Queries', 0),
('affinity I/O mask', 0),
('affinity64 I/O mask', 0),
('affinity mask', 0),
('affinity64 mask', 0),
('Agent XPs',0),
('allow updates', 0),
('backup compression default', 0),
('blocked process threshold', 0),
('c2 audit mode', 0),
('clr enabled', 0),
('common criteria compliance enabled', 0),
('contained database authentication', 0),
('cost threshold for parallelism', 5),
('cross db ownership chaining', 0),
('cursor threshold', -1),
('Database Mail XPs', 0),
('default full-text language', 1033),
('default language', 0),
('default trace enabled', 1),
('disallow results from triggers', 0),
('EKM provider enabled', 0),
('filestream_access_level', 0),
('fill factor(%)', 0),
('ft crawl bandwidth (max)', 100),
('ft crawl bandwidth (min)', 0),
('ft notify bandwidth (max)', 100),
('ft notify bandwidth (min)', 0),
('index create memory(KB)', 0),
('in-doubt xact resolution', 0),
('lightweight pooling', 0),
('locks', 0),
('max degree of parallelism', 0),
('max full-text crawl range', 4),
('max server memory(MB)', 2147483647),
('max text repl size(B)', 65536),
('max worker threads', 0),
('media retention', 0),
('min memory per query(KB)', 1024),
('min server memory(MB)', 0),
('nested triggers', 1),
('network packet size(B)', 4096),
('Ole Automation Procedures', 0),
('open objects', 0),
('optimize for ad hoc workloads', 0),
('PH timeout(s)', 60),
('precompute rank', 0),
('priority boost', 0),
('query governor cost limit', 0),
('query wait(s)', -1),
('recovery interval(min)', 0),
('remote access', 1),
('remote admin connections', 0),
('remote login timeout(s)', @temp),
('remote proc trans', 0),
('remote query timeout(s)', 600),
('Replication XPs', 0),
('scan for startup procs', 0),
('server trigger recursion', 1),
('set working set size', 0),
('show advanced options', 0),
('SMO and DMO XPs', 1),
('transform noise words', 0),
('two digit year cutoff', 2049),
('user connections', 0),
('user options', 0),
('xp_cmdshell', 0);

-- now pull back which ones you're using that aren't defaults
SELECT
s.name [Configuration],
s.value_in_use [Yours],
d.Value [Default]
FROM
@defaults d JOIN sys.configurations s
 ON s.name LIKE '%' + d.config + '%'
 AND d.Value <> s.value_in_use
WHERE
s.name <> 'show advanced options'
ORDER BY
s.name

These are the results from my own instance:

That's about it.  Now there are many different opinions about which configs should be changed, and to what. I've got my changes that I make on every server I manage, and then there are other changes that I make, for different needs. Then, of course, there are countless other recommendations from the other SQL experts out there. These are two of my favorites:  

www.brentozar.com - Five SQL Server Settings to Change
http://sqlblog.com  - Did You Know? What settings to always change

I would also be sure to take a look at this one for more detail on sp_configure:

     https://msdn.microsoft.com/en-us/library/ms188787.aspx