cancel
Showing results for 
Search instead for 
Did you mean: 

Foolproof way to get table column list

Former Member
5,017

There seems to be some difference's between versions of SQL Anywhere on how to get a list of columns and data types for a table. This is the SQL I use:

 select A.name, 
 C.name as datatype, 
 A.length, 
 A.prec, 
 A.scale, 
 CASE A.status WHEN 8 THEN 1 ELSE 0 END
 FROM dbo.syscolumns A, 
 dbo.systypes C  
 WHERE A.id = Object_Id('MyTableNameHere')
 AND A.usertype = C.usertype  
 ORDER BY A.colid

In some cases, it appears that it doesn't pick up all the columns for a table. Is it the join to systypes?

What SQL can I use across all versions to extract the information in the above query?

Thanks,

Brad

Accepted Solutions (1)

Accepted Solutions (1)

graeme_perrow
Advisor
Advisor

I suspect that dbo.syscolumns doesn't list columns that use custom domains. Here's an almost equivalent statement:

select c.column_name,
       case c.nulls when 'Y' then 1 else 0 end as nulls,
       d.domain_name,
       c.width,
       d."precision",
       c.scale 
       from sys.syscolumn c join sys.sysdomain d on c.domain_id=d.domain_id
       join sys.systable t on c.table_id=t.table_id
       where t.object_id=Object_Id('MyTableNameHere')
Former Member
0 Kudos

Thanks a lot. I'll get some people to test it out.

reimer_pods
Participant
0 Kudos

I'v noticed that this statement runs only on version 10 and later, presumably due to changes in then structure of the system tables

Breck_Carter
Participant

@Reimer: Change the WHERE clause to where t.table_name='whatever' and it works for versions 5.5 through 11.0.1. Also note, if there are multiple tables with the same name but different owners, you really need to add a predicate for the owner.

Answers (0)