February 17, 2003 at 6:37 am
Is there a simple way to determine the columntype inm SQL ?
If a column is varchar(85) i want to see -> Varchar(85)
so the simple string representation.........
Has somebody figured this out already ?
February 17, 2003 at 6:52 am
Query the system tables, specifically the columns and the datatype tables
declare @tbl varchar(255)
declare @col varchar(255)
set @tbl = TableName
set @Col = ColumnName
select systypes.Name + '(' + cast(syscolumns.length as varchar(100)) + ')'
from syscolumns
inner join systypes on systypes.xtype = syscolumns.xtype
where syscolumns.name = @Col
and Id = object_Id(@Tbl)
Although you may have to do a bit of tweeking with datatypes where you would not necessarily want to see the length (ie datetime, rather then datetime(8))
February 17, 2003 at 7:17 am
DOes this one also works for usertypes ?
February 17, 2003 at 8:20 am
It does, as usertypes are also stored in the systypes table.
There are complications though, as usertypes share the same xtypeId as their parent datatype, so this query returns both.
I'm sure there must be a method using the fields in the systype table to filter this down, it just may take a bit of fiddling.
One further thing to note, as well as the datetime type previously mentioned, for other datatypes, such as decimal, you would probably want to substitute it for prec(sion) and scale, both of which are separate fields.
February 18, 2003 at 5:51 am
Well something weird went on with those usertypes so no i changed you rquery to :
declare @tbl sysname
set @tbl = 'table2'
select sc.name as 'ColumnName',
case bt.Name
when 'Int' then 'Integer'
when 'Image'then 'Image'
When 'Text' then 'Text'
when 'Varchar' then 'Varchar('+ rtrim(ltrim(str(sc.length)))+')'
when 'NVarchar' then 'NVarchar('+ rtrim(ltrim(str(sc.length)))+')'
when 'Nchar' then 'NChar('+ rtrim(ltrim(str(sc.length)))+')'
when 'char' then 'Char('+ rtrim(ltrim(str(sc.length)))+')'
when 'varbinary' then 'varbinary('+ rtrim(ltrim(str(sc.length)))+')'
ELSE td.name
END as 'Columntype'
from syscolumns sc
left join systypes td on td.xusertype = sc.xusertype -- and systypes.type = systypes.xtype
left join systypes bt on bt.xusertype = td.xtype
where Id = object_Id(@Tbl)
order by sc.name
But I really don't know if i got it right ?
Does any of the GURU's out there know if this is the right way ?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply