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





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:



Thanks for reading this article.