Capturing Information of Configuration in SQL Server Using Sys.configurations

Introduction

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 the columns used in the 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

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.