Introspecting IDENTITY columns in Sybase SQL Anywhere

Question

For a table such as:

create table foo (id int identity primary key)

I would like to query the system tables to determine that id is in fact an IDENTITY column.

The closest thing I found was:

select tc.max_identity from systabcol tc
join systab t on t.table_id = tc.table_id
where t.table_name = 'foo'

Unfortunately max_identity is 0 even for columns which are not identities, and is also 0 for identities which haven't been used yet. Would have been nice if it was NULL for non-identities.

I can use the GET_IDENTITY function to check if a table has an IDENTITY column, but then I lose an identity value and I still don't know which column it is.

Any help much appreciated, this is the last thing I need for basic SQL Anywhere support in DBIx::Class::Schema::Loader in 0.05002 .

Answer 1

SyBooks Online note the status column. This is ASE, but it might be the same.

Answer 2

It seems I can use the "default" column in SYSTABCOL:

select tc.[default]
from systabcol tc
join systab t on t.table_id = tc.table_id
where t.table_name = 'foo'

when the "default" column is set to 'autoincrement' then it's an IDENTITY column.