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
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.