Search This Blog

xType on syscolumns

Unlike 'xType' in sysObjects, you wont be able to find what exactly those values mean in syscolumns table as in the BOL it is written mentioned as 'For Internal purpose'. The sysstem table which stores this information is sysTypes

SELECT xtype, name FROM systypes

and the results

XType Datatype
34 image
35 text
36 uniqueidentifier
48 tinyint
52 smallint
56 int
58 smalldatetime
59 real
60 money
61 datetime
62 float
98 sql_variant
99 ntext
104 bit
106 decimal
108 numeric
122 smallmoney
127 bigint
165 varbinary
167 varchar
173 binary
175 char
189 timestamp
231 nvarchar
231 sysname
239 nchar
241 xml


Eduthrash said...

Great post, simple, but efective, I was searching for this.


Tony Wan said...

This piece of information is just me a lot of times

Thank you

Raul Ornelas said...

check this out

SELECT AS Tabla, AS Columna, AS Tipo,
sc.max_length AS Tamaño
FROM sys.objects so INNER JOIN sys.columns sc
ON so.object_id = sc.object_id INNER JOIN sys.types st
ON st.system_type_id = sc.system_type_id
AND != 'sysname'
WHERE so.type = 'U'