Q: How in DMV can I convert data_type field from integer to string representation?
User Rating: / 0
PoorBest 
Written by Vidas Matelis   
Tuesday, 20 January 2009 03:05

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_TYPETYPE_NAMECOLUMN_SIZEIS_FIXEDLENGTH
2SHORT2TRUE
3LONG4TRUE
4FLOAT4TRUE
5DOUBLE8TRUE
6CURRENCY8TRUE
7DATE4TRUE
8BSTR256FALSE
18USHORT2TRUE
19ULONG4TRUE
129CHAR256FALSE
130WCHAR256FALSE
12VARIANT16TRUE

Done 

 
Comments (1)
1 Wednesday, 09 November 2011 11:22
Farid
what is data_type 20?
It is not in your list

Tags: 2008, dmv, faq
 

XL Cubed