Tutorial :Introspecting IDENTITY columns



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 .


Solution:1

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


Solution: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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »