The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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

asked 09 Dec '09, 15:03

Brad%20Wery's gravatar image

Brad Wery
382182026
accept rate: 0%

edited 12 Dec '09, 09:12

Breck%20Carter's gravatar image

Breck Carter
26.6k418577824

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

Graeme Perrow
8.4k369107
accept rate: 51%

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×24

question asked: 09 Dec '09, 15:03

question was seen: 2,085 times

last updated: 12 Dec '09, 09:12