Capturing Information of Configuration in SQL Server Using Sys.configurations

This article explains how to get server-wide configuration option values in the system.

In total, there are 64 configurations in SQL Server. See the screenshots below.

The following is the description of columns used in configuration:

  • configuration_id

    Unique ID for the configuration value.
     
  • Name

    Name of the configuration option.
     
  • Value

    Configured value for this option.
     
  • Minimum

    Minimum value for the configuration option.
     
  • Maximum

    Maximum value for the configuration option.
     
  • value_in_use

    Running value currently in effect for this option.
     
  • Description

    Description of the configuration option.
     
  • is_dynamic

    1 = The variable that takes effect when the RECONFIGURE statement is executed.
     
  • is_advanced

    1 = The variable is displayed only when the show advanced option is set.

Syntax:

SELECT * FROM sys.configurations

OUTPUT

SQL1.jpg

SQL2.jpg

Configuring CLR integration

Now we are going to configure the CLR, by enabling it.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE
;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE
;
GO

Disabling

sp_configure 'show advanced options', 1;
GO
RECONFIGURE
;
GO
sp_configure 'clr enabled', 0;
GO
RECONFIGURE
;
GO

Query:

SELECT * FROM sys.configurations WHERE name = 'clr enabled'

OUTPUT:

SQL3.jpg

Thanks for reading this article.