http://www.sqlservercentral.com/blogs/stratesql/2012/07/07/lost-in-translation-deprecated-system-tables-syscurconfigs/

Printed 2014/10/30 11:52AM

Lost in Translation – Deprecated System Tables – syscurconfigs

2012/07/07

4957867646_2f2478fd69_m5 This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objectsYou can find a list of all of the deprecated views in the introduction post.

The compatibility view syscurconfigs returns rows for each configuration option within SQL Server that can be set by the user.  The options that can be configured are those such as the maximum degree of parallelism and whether to allow remote connections to the SQL Server.  The value returned for each configuration value presented are those that the SQL Server is currently running with.  Any configuration changes that have been made, but not applied, will not be represented in the results.  This is the key difference between this compatibility view and sysconfigures; which returns the values that will take affect after the next RECONFIGURE or service restart.

As with the sysconfigures compatibility view, syscurconfigs is also replace with the catalog view sys.configurations.  This catalog view provides the same information as the compatibility view.  Along with those columns, there are a few more columns provided which assist in managing configuration options more easily.

Status Column

Syscurconfigs also contains a status column that provides information similar to data available in the sysconfigures status column.  The column contains two known bit values.  These values are:

Query Via syscurconfigs

Queries against syscurconfigs are often pretty basic.  Besides decoding the status column, the rest of the columns are returned as they are.  The code in Listing 1 can be used to query the compatibility view.


Listing 1 – Query for syscurconfigs

SELECT value
,config
,comment
,CONVERT(smallint,status & 0x1) / 1 AS is_dynamic
,CONVERT(smallint,status & 0x2) / 2 AS is_advanced
FROM syscurconfigs

Additional Information

The introduction of the catalog views allows for some additional information to be retrieved about schema objects which were previously difficult to ascertain.  These are available in the following columns:

Query via Catalog Views

The query in Listing 2 retrieves the same information from sys.configurations as the query for syscurconfigs.  The chief difference between the queries is the lack of a need to decode the status column and the inclusion of the configured value with the running value.


Listing 2 – Query for sys.configures 

SELECT c.configuration_id AS config
    ,c.description AS comment
    ,c.is_dynamic
    ,c.is_advanced
    ,c.name
    ,c.value_in_use
    ,c.value
    ,c.minimum
    ,c.maximum
FROM sys.configurations c

Summary

In this post, we discussed the use of sys.configurations over syscurconfigs.  In this case, changing from using the deprecated compatibility view to the catalog view is a relatively simple task; which also provides the information from two compatibility views.  There are few changes to the columns and the dataset is represented in the same manner.

Do you see any reason to continue using syscurconfigs?  Is there anything missing from this post that people continuing to use the compatibility view should know?

Original article: Lost in Translation – Deprecated System Tables – syscurconfigs

©2012 Strate SQL. All Rights Reserved.

Related posts:

  1. Lost in Translation – Deprecated System Tables – sysconfigures
  2. Lost in Translation – Deprecated System Tables – sysdepends
  3. Lost in Translation – Deprecated System Tables – sysaltfiles


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.