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

The compatibility view syscolumns returns one rows for each column and parameter in the database.  The column rows represent the columns in the result sets for every table, view, and table-valued function.  While the parameter rows represent the parameters that are passed into stored procedures and functions.

There are four catalog views designed to replace the syscolumns, when it is removed.  These views are sys.all_columns, sys.columns, sys.all_parameters, and sys.parameters.  The outputs from the views sys.all_columns and sys.columns represent the columns for tables, views, and table-valued functions.  Similarly, the views sys.all_parameters and sys,parameters returns results with all parameters used in stored procedures and functions.  The use of the “all_” prefix on two of the catalog views indicates that the views return results for both system and user defined objects, where those without the prefix only include user defined objects.  This distinction is especially useful with you write queries against these objects and are only concerned with user objects.

Informational Columns

A number of columns in the syscolumns compatibility view are noted as being informational columns and unsupported.  Unlike other compatibility views where unsupported columns contain hidden information within the bit values in the columns, many of these do not appear anymore else where in the SQL Server.  These columns include:

• typestat
• xoffset
• bitpos
• reserved
• colstat
• autoval
• collationid
• printfmt

A few of the columns listed as informational can be mapped to other columns in the catalog views noted in the introduction.  Two of the columns xprec and xscale correlate to the precision and scale columns.  The colorder column correlates to the column_id column in the catalog views.

Status Column

Along with the informational columns, the status column in syscolumns stores a number of properties about the column.  These properties are:

• 0×08: Identifies whether the column allows NULL values.
• 0×10: Indicates when ANSI padding has been applied.
• 0×40: Identifies whether the data in the row is for a parameter.
• 0×80: Indicates whether the column has the identity property assigned.

In the catalog views, the third property (0X40) is handled by whether the row is in the catalog view sys.parameters or sys.columns.  For the other three properties, the values are in the is_nullable, is_ansi_padded, or is_identity columns.

Query Via syscolumns

An example of a query to return the information of use from syscolumns is provided in Listing 1.  Of course, the results will be for both columns and parameters in the database.  If you needed one or the other, or needed to only show user-defined object columns and parameters, a WHERE clause would need to be added to the results.  All of the columns, except the informational columns, are included in the query.

Listing 1 – Query for syscolumns 
SELECT name
    ,id
    ,xtype
    ,xusertype
    ,length
    ,colid
    ,cdefault
    ,domain
    ,number
    ,offset
    ,status
    ,CONVERT(INT,status & 0x8) / 8 AS is_nullable
    ,CONVERT(INT,status & 0x10) / 16 AS is_ansi_padded
    ,CONVERT(INT,status & 0x40) / 64 AS is_parameter
    ,CONVERT(INT,status & 0x80) / 128 AS is_identity
    ,type
    ,usertype
    ,prec
    ,scale
    ,iscomputed
    ,isoutparam
    ,isnullable
    ,collation
FROM sys.syscolumns 

Additional Information

With the expansion of features to SQL Server since the introduction of catalog views and deprecation of compatibility views, there are a number of new properties that can be applied to columns and parameters.

When examining the new information for columns, one of the main changes is the removal of the status column and it’s replacement with columns that represent the properties directly.  These new columns are the is_nullable, is_ansi_padded, and is_identity columns.  There are replication columns; which include is_replicated, is_non_sql_subscribed, is_merge_published, and is_dts_replicated.  Relationship columns tying the column to other objects, such as XML documents, defaults, and rules.  Along with columns to determine whether the column is filestream, sparse, or part of a column set.

On the parameter side, the catalog view also contains additional and new information.  The new columns identify where the parameter has a default value, if it is an XML document, and whether the parameter is read only

Column Query via Catalog Views

As mentioned in the introduction, the replacement catalog views for syscolumns split the rows of the compatibility view between two sets of catalog views.  The first set are the catalog views for columns.  The query in Listing 2, returns the columns for the tables, views, and table-valued functions in the database.  To return only those for user-defined objects, use sys.columns in the query instead of sys.all_columns.

Listing 2 – Query for columns
SELECT c.name
   ,c.object_id AS id
   ,t.system_type_id AS xtype
   ,t.user_type_id AS xusertype
   ,t.max_length AS length
   ,c.column_id AS colid
   ,c.default_object_id AS cdefault
   ,c.rule_object_id AS domain
   ,t.system_type_id AS type
   ,t.user_type_id AS usertype
   ,c.precision AS prec
   ,c.scale AS scale
   ,c.is_computed AS iscomputed
   ,c.is_nullable
   ,c.is_ansi_padded
    ,c.is_identity
   ,c.collation_name AS collation
FROM sys.all_columns c
    INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

Parameter Query via Catalog Views

The other set of catalog views represents the all of the parameters that are available for stored procedures and functions.  The parameters for all objects can be returned using the query in listing 3.  Alternatively, only those for user-defined objects can be accessed by using sys.parameters instead of sys.all_parameters.

Listing 3 – Query for parameters
SELECT c.name
   ,c.object_id AS id
   ,t.system_type_id AS xtype
   ,t.user_type_id AS xusertype
   ,t.max_length AS length
   ,c.parameter_id AS colid
   ,NULL AS cdefault
   ,NULL AS domain
   ,t.system_type_id AS type
   ,t.user_type_id AS usertype
   ,c.precision AS prec
   ,c.scale AS scale
   ,NULL AS iscomputed
   ,NULL AS is_nullable
   ,NULL AS is_ansi_padded
   ,NULL AS is_identity
   ,NULL AS collation
FROM sys.all_parameters c
    INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

Summary

In this post, we discussed the use of syscolumns and the catalog views that are replacing it.  For the two sets of data, for columns and parameters, there are methods for viewing the data for all objects and user-defined objects.  By using the catalog views, you are better able to retrieve the column and parameter information without having to filter the data to remove information that isn’t needed – since we often don’t want both column and parameter information at the same time.

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

Related posts:

1. Lost in Translation – Deprecated System Tables – syscomments
2. Lost in Translation – Deprecated System Tables – sysconfigures
3. Lost in Translation – Deprecated System Tables – syscurconfigs

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

©2012 Strate SQL. All Rights Reserved.