A Script A Day - Day 18 - Viewing System Configuration

Today’s script is something I’ve learned today, it’s cool learning new stuff!  When viewing server configurations in the past I’ve used the below;

EXEC sp_configure 'show advanced options',1;

GO

RECONFIGURE WITH OVERRIDE;

GO

EXEC sp_configure;

GO

This is all fine when making changes to the system configuration and you need to run the RECONFIGURE, but the RECONFIGURE command flushes the buffer cache!

An alternative is to use sys.configurations as below.  Check it out, I like it anyway.

/*

      -----------------------------------------------------------------

      Viewing System Configuration

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Change db context

USE master;

GO

-- System Configuration

SELECT

      name,

      value,

      value_in_use,

      [description]

FROM

      sys.configurations

ORDER BY

      name ASC;

GO

Enjoy!

Chris