Search This Blog

Loading...

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
ORDER BY xType

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

3 comments:

Eduthrash said...

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

Thanks!!

Tony Wan said...

This piece of information is just brilliant...save me a lot of times

Thank you

Raul Ornelas said...

check this out

SELECT so.name AS Tabla,
sc.name AS Columna,
st.name 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 st.name != 'sysname'
WHERE so.type = 'U'
ORDER BY so.name, sc.name