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 systypes returns one row for data type in the context of a database.  For the data types, both system and user defined data types are included.

The catalog view that replaces systypes is sys.types.  In similar fashion, the new catalog view returns one row for every system and user defined database within a database.

Status Column

The systypes compatibility view has a status column, like many other compatibility views.  As with some others, the status column in systypes represents a single value.  The value 0×1 identifies whether the data type can be nullable.  This value is the inverse of the allownulls column in the compatibility view.

Query Via systypes

When querying systypes, there are a couple things to keep in mind.  First, there is the status column as mentioned in the previous section, this can be translated out, but doesn’t necessarily need to because of the allownulls column.  Second, there are some columns which do not contain meaningful data any longer.  The columns reserved and printfmt are no longer supported and now only return 0 or null, respectively.  Considering these items, the query in Listing 1 represents a query for all of the data in systypes.

--Listing 1 – Query for sys.systypes
SELECT name
,xtype
,xusertype
,length
,xprec
,xscale
,tdefault
,domain
,uid
,reserved
,collationid
,usertype
,variable
,allownulls
,type
,printfmt
,prec
,scale
,collation
,status
,CONVERT(INT,status & 0x1) / 1 is_not_nullable
FROM systypes

Query via sys.types

There is a bit of complexity when it comes to mapping systype to the catalog view sys.types.  The easiest piece pertains to the reserved and printfmt columns, since they only return dummy values in systypes.  Beyond that though, there is first the renaming of the columns.  For the most part, all of the column names have changed between the two views, except for the name column.  Next the collation ID value requires the COLLATIONPROPERTY function.  Another function required is TYPEPROPERTY which provides the usertype, scale, and prec values in systypes.  The last function needed is the undocumented XTYPETOTDS function for the type column.  Pulling these together results in the query in Listing 2.

--Listing 2 – Query for sys.types
SELECT name
,system_type_id AS xtype
,user_type_id AS xusertype
,max_length AS length
,precision AS xprec
,scale AS xscale
,default_object_id AS tdefault
,rule_object_id AS domain
,schema_id AS uid
,0 AS reserved
,COLLATIONPROPERTY(collation_name, 'collationid') AS collationid
,TYPEPROPERTY(name, 'oldusertype') AS usertype
,case when system_type_id in (165, 167, 231) then 1 else 0 end AS variable
,is_nullable AS allownulls
,XTYPETOTDS(system_type_id, 0) AS type
,NULL AS printfmt
,case when system_type_id not in (34, 35, 99) then TYPEPROPERTY(name, 'precision') end AS prec
,convert(tinyint, TYPEPROPERTY(name, 'scale')) AS scale
,collation_name AS collation
,principal_id
,is_user_defined
,is_assembly_type
,is_table_type
FROM sys.types

Outside of the columns in systypes, there are some additional columns provided in sys.types that are rather useful.  There is an is_user_defined column which identifies user defined data types.  The is_assembly_type column helps to identify CLR based data types, and finally the is_table_type identifies data types that are defined for table-valued parameters.

Summary

In this post, we compared the compatibility view systypes with the catalog view sys.types.  There is some complexity in translating between on and the other.  Where it counts, though, the translation between the two is rather simple.  After reading all of this, do you see any reason to continue using systypes?  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 – sysindexkeys
2. Lost in Translation – Deprecated System Tables – sysmessages
3. Lost in Translation – Deprecated System Tables – sysmembers