2

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 .

Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
Rafael Kitover
  • 967
  • 10
  • 13

2 Answers2

1

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

Paul Creasey
  • 28,321
  • 10
  • 54
  • 90
0

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.

Rafael Kitover
  • 967
  • 10
  • 13