How to find out data type in IQ

Question

I want to make a select statement that shows me all user tables, columns, data types and data length of the selected columns.

I have done it already in ASE but the system tables in IQ are different and I can't join the necessary tables to get my select. In ASE the code looks like this:

select so.name as 'table name', sc.name as 'column name', st.name as 'data type', sc.length as 'data  length'    
from sysobjects so
inner join syscolumns sc on so.id = sc.id
inner join systypes st on sc.usertype = st.usertype
where so.type = 'U' 

I used the usertype as a join key to get what I want, but in IQ the syscolumn and systypes can't be joined like that, anyone know how I can get it done?

Answer 1

select
  t.table_name,
  c.column_name,
  d.domain_name,
  c.width,
  c.scale
FROM SYS.SYSTAB t
JOIN SYS.SYSCOLUMN c
  ON t.table_id = c.table_id
join SYS.SYSDOMAIN d
  on d.domain_id = c.domain_id
where t.creator <> 0 --sysobjects type 'S'
and not exists (select * from sys.systab tv 
                where tv.creator in (2, 22) 
                and tv.table_id = t.table_id) --2/22 sysobjects type 'V'