SAP SQL Anywhere

Question

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 Answer

3

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')

permanent link

answered 09 Dec '09, 18:22

Graeme Perrow
9.6k●3●79●124
accept rate: 54%

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

(09 Dec '09, 20:51) Brad Wery

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

(10 Dec '09, 09:26) Reimer Pods

Comment Text Removed

1

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

(10 Dec '09, 12:08) Breck Carter

flat view

SAP SQL Anywhere

Foolproof way to get table column list

Follow this question

Related questions