I am looking for functions/tools that would help me to find the
datatype of a column. The input parameters would be tablename
and column name.
In C you have dbcoltype() (DBlibrary) or ct_describe() (CTlibrary).
In T-SQL, you'd have to write something like this:
select Type = t.name, Column_name = c.name
from syscolumns c, systypes t
where c.id = (select id from sysobjects where name = 'table_name')
and c.usertype *= t.usertype
and c.name = 'column_name'
Hope this helps...
Michael Peppler, ITF Management SA, Fontaine 13, CH-1204 Geneva
GCS d+(-) s++: a C++ US++++$ P+++$ E++ W? N++ !w tv- b+++ G e+++ h---
The basic query you want is:Quote:> I am looking for functions/tools that would help me to find the
> datatype of a column. The input parameters would be tablename
> and column name.
select Column_name = c.name,
Type = t.name,
Length = c.length,
Prec = c.prec,
Scale = c.scale,
Nulls = convert(bit, (c.status & 8)),
Default_name = object_name(c.cdefault),
Rule_name = object_name(c.domain),
rtype = t.type, utype = t.usertype,
Ident = convert(bit, (c.status & 0x80))
from syscolumns c,
where c.usertype *= t.usertype
This is a minor adaptation of the relevant code from sp_help - the system
procedures are nearly always the best place to look for example code.
AFAIK there is no system function that returns this information.
The SQL Workshop
Milton Keynes, UK
How do I find out which datatype a column has in a temporary table?
sp_columns does not work on temporary tables, only on tables.