ASE Database forum - retrieving all types for all columns

Author

retrieving all types for all columns

Todd

2005-04-21, 8:24 pm

I'm trying to get a select list with all the columns in a
table and all types for each column. If I don't filter the
join on systypes with "c.usertype = t.usertype", then I get
things like numeric and numericn for the same column. But
if I include this, then sometimes I don't get all the
columns.

What is the proper SQL to do this?

SELECT o.name as table_name, c.colid, c.name as column_name,
c.type, t.name as type_name, c.length, c.prec, c.scale,
(select 1 from syscolumns c2
where c2.status & 128 = 128
and c2.id = c.id
and c2.colid = c.colid) as ident_col
FROM syscolumns c
JOIN systypes t ON c.type = t.type AND c.usertype
= t.usertype
JOIN sysobjects o ON o.id = c.id

Rob Verschoor

2005-04-21, 8:24 pm

This can be quite tricky if you want to do this for all datatypes. I don't
have the exact queries handy, but a working implementation can be found in
the code of sp_rv_findobject, which you can download from
www.sypron.nl/new_ssp.html.

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase Replication Server Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

<Todd> wrote in message news:42681ad3.733.1681692777@sybase.com...
> I'm trying to get a select list with all the columns in a
> table and all types for each column. If I don't filter the
> join on systypes with "c.usertype = t.usertype", then I get
> things like numeric and numericn for the same column. But
> if I include this, then sometimes I don't get all the
> columns.
>
> What is the proper SQL to do this?
>
> SELECT o.name as table_name, c.colid, c.name as column_name,
> c.type, t.name as type_name, c.length, c.prec, c.scale,
> (select 1 from syscolumns c2
> where c2.status & 128 = 128
> and c2.id = c.id
> and c2.colid = c.colid) as ident_col
> FROM syscolumns c
> JOIN systypes t ON c.type = t.type AND c.usertype
> = t.usertype
> JOIN sysobjects o ON o.id = c.id

2005-04-22, 9:24 am

The link between systypes and syscolumns is the usertype
columns.
You can drop the c.type = t.type

SELECT
o.name as table_name
, c.colid
, c.name as column_name
, c.type
, convert(char(15), t.name) as type_name
, c.length
, isnull(str(c.prec, 5,0), ' ') as prec
, isnull(str(c.scale, 5,0), ' ') as scale
, isnull((select 'Idnty' from syscolumns c2 where
c2.status & 128 = 128 and c2.id = c.id and c2.colid =
c.colid), ' ')
as ident_col
, case when convert(bit,(c.status & 8)) = 0 then 'N'
else 'Y' end as "NULL"
FROM syscolumns c, systypes t, sysobjects o
where c.usertype = t.usertype
and o.id = c.id
and o.type = 'U'
order by 1, 2

> I'm trying to get a select list with all the columns in a
> table and all types for each column. If I don't filter
> the join on systypes with "c.usertype = t.usertype", then
> I get things like numeric and numericn for the same
> column. But if I include this, then sometimes I don't get
> all the columns.
>
> What is the proper SQL to do this?
>
> SELECT o.name as table_name, c.colid, c.name as
> column_name,
> c.type, t.name as type_name, c.length, c.prec, c.scale,
> (select 1 from syscolumns c2
> where c2.status & 128 = 128
> and c2.id = c.id
> and c2.colid = c.colid) as ident_col
> FROM syscolumns c
> JOIN systypes t ON c.type = t.type AND
> c.usertype = t.usertype
> JOIN sysobjects o ON o.id = c.id

Sponsored Links