I'm not sure if this has much practical application, but I thought it may be useful for information's sake. The following script will return a list of data type id's with the SQL data name representing the type. I used the type_name function when querying the syscolumns tables and needed a plain english column type.
DECLARE @typeid int
DECLARE @typename varchar(50)
SET @typeid =0
WHILE (@typeid < 500)
BEGIN
SELECT @typename = type_name (@typeid )
IF (@typename IS NOT NULL)
PRINT convert(varchar(5), @typeid ) + ' ' + @typename
SET @typeid = @typeid + 1
END
This script will also return the names and id's of all a db's user defined types, if any have been created.
The result set looks something like this
0, void type
1, table
31, void type
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
239, nchar
241, xml
242, xml
250, cursor
256, sysname
257, StringGuid (A UDT I added for the demo)