Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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, as datatype, 
 FROM dbo.syscolumns A, 
 dbo.systypes C  
 WHERE = 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?



asked 09 Dec '09, 15:03

Brad%20Wery's gravatar image

Brad Wery
accept rate: 0%

edited 12 Dec '09, 09:12

Breck%20Carter's gravatar image

Breck Carter

@Brad: Here's a tip: Study the Version 9 catalog tables, and NEVER EVER use any of the SYS. or DBO. view names, either in V9 or earlier, or in V10 and later (where everything changed). The old views like SYSCOLUMNS suck big wampum, lead to confusion and worse... stick to the REAL TABLES. The kicker comes in V10 and later, when all the old tables became views themselves... but these views are designed for backward compatibility and all but one of them work quite well. Bottom line, never use SYSCOLUMNS, but SYSCOLUMN is ok. An expanded rant is available on request :)

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

Breck, I would love a more expanded rant personally.

(12 Dec '09, 02:10) Calvin Allen

@Breck: Me, too:)

(12 Dec '09, 21:10) Volker Barth

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,
       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%20Perrow's gravatar image

Graeme Perrow
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

@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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 09 Dec '09, 15:03

question was seen: 4,628 times

last updated: 12 Dec '09, 09:12