Q: How in DMV can I convert data_type field from integer to string representation?

Q: How in DMV can I convert data_type field from integer to string representation?

A: Certain DMVs (for example $system.MDSCHEMA_MEASURES) used data_type field to describe measure type. This field is integer type. To get a textual data type presentation you will need to use DMV $SYSTEM.DBSCHEMA_PROVIDER_TYPES. Here is query to get a list of available data types:

SELECT DATA_TYPE, TYPE_NAME, COLUMN_SIZE, IS_FIXEDLENGTH
 FROM $SYSTEM.DBSCHEMA_PROVIDER_TYPES

Here is a sample result:

 

DATA_TYPE	TYPE_NAME	COLUMN_SIZE	IS_FIXEDLENGTH
2	SHORT	2	TRUE
3	LONG	4	TRUE
4	FLOAT	4	TRUE
5	DOUBLE	8	TRUE
6	CURRENCY	8	TRUE
7	DATE	4	TRUE
8	BSTR	256	FALSE
18	USHORT	2	TRUE
19	ULONG	4	TRUE
129	CHAR	256	FALSE
130	WCHAR	256	FALSE
12	VARIANT	16	TRUE

Done