syscolumns type number names - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

syscolumns type number names

Author

Topic

Sundog105
Starting Member

1 Post

Posted - 2006-05-19 : 10:31:25

In syscolumns, the field syscolumns.type returns numbers that relate to the data types. I need a reference that maps these numbers to names. E.g. 106 = decimal. (This is so I can search for columns with data types such as numeric that need to be changed to int.)

Sundog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-19 : 10:34:10

select * from information_schema.columns where table_name='TableName'

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-19 : 19:47:45

[code]select object_name(c.id) as table_name, c.name as column_name, t.name as data_type
from syscolumns c inner join systypes t
on c.xtype = t.xtype
where c.id = object_id('TableName')[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-20 : 01:57:04

quote:

Originally posted by khtan

select 	object_name(c.id) as table_name, c.name as column_name, t.name as data_type
from	syscolumns c inner join systypes t
	on	c.xtype	= t.xtype
where	c.id	= object_id('TableName')

This will return more columns than expected(with sysname as datatype)

I think this is simple

select table_name,column_name,data_type from information_schema.columns where table_name='Yourtable'

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-20 : 09:12:18

It depends what you want as the result
systypes does decode the value but you need to use xusertype to decide on which value to choose.
You can use the function type_name to do this.

try
select type_name(xusertype), name from syscolumns where id = object_id('mytbl')
select type_name(xtype), name from syscolumns where id = object_id('mytbl')
or as madhivanan suggests
select column_name,data_type from information_schema.columns where table_name='Yourtable'

But you have to be careful about the table name here - should be ok for user tables but otherwise the name might not be what you expect.

They will give different results in some cases so it depends what you want to do with it.
Have a look at sp_help to see what that does.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources