System function finding the datatype of a column

System function finding the datatype of a column

Post by Jerry Math » Thu, 31 Aug 1995 04:00:00



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.

Thanks

 
 
 

System function finding the datatype of a column

Post by Michael Peppl » Fri, 01 Sep 1995 04:00:00




>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.

What do you mean? An API function (like in C) or something eles at the
SQL level?

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
--
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---
r+++ y?

 
 
 

System function finding the datatype of a column

Post by Simon Walke » Fri, 01 Sep 1995 04:00:00


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.

> Thanks

The basic query you want is:

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,
        systypes t
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.

Regards,

simon walker
The SQL Workshop
Milton Keynes, UK