How to get all server set configurations from SQL Server management studio.

Problem: How to get all server set configurations from SQL Server management studio.

Solution: sys.configurations

Under server- wide configuration catalog views, there is a system object view which stores a row for result for server wide configuration option value in system.

This kind of helps when you what state each configuration values or and gives a way to evaluate or change some decisions when a setting is turned or off.

To Know what a configuration option means, please go through all the list of  Server Configuration Options (SQL Server).

There are about 70 rows in SQL 2014. It differs from each version based on the SQL Server flavor and the configurations they released in that year.

Example:

In this article I am going to show you we can retrieve the full text default language configured inside the server.

SELECT

               c.NAME AS configuration_name,

                l.NAME AS language_name,

                c.value AS configurion_value

FROM sys.configurations c

INNER JOIN sys.syslanguages l

                ON l.lcid = c.value

WHERE c.NAME = 'default full-text language'

OUTPUT:

configuration_name	language_name	configurion_value
default full-text language	us_english	1033