On 01-07-2010 08:08:14 -0400, Mark Bucciarelli wrote:

Hi,

Given a table with a sequence that controls the primary id, is there a SQL command I can run to get the name of that sequence?

Yes, it's stored in the sequence table iirc. JDBC and msqldump have queries to retrieve them (to reconstruct a create sequence statement).

Alternatively, is there a way to specify the sequence name when creating the table?

how about: create sequence "bla" ... create table "foo" (id sequence "bla");

(plus/minus obvious mistakes in SQL)

On 2010-07-01 14:08, Mark Bucciarelli wrote:

Hi,

Given a table with a sequence that controls the primary id, is there a SQL command I can run to get the name of that sequence?

Alternatively, is there a way to specify the sequence name when creating the table?

Thanks,

Sequence names are stored in the table sys.sequences. The association between a column and the sequence from which it gets its default value is stored in the table sys.columns. However, that is stored as a snippet of SQL code.

You can use sequences with explicit names as in the following example (also see the output of msqldump):

create sequence foo_sequence as integer; create table foo (i int default next value for foo_sequence);

If you now do a select on sys.columns, you'll see: sql>select "default" from sys.columns where table_id = (select id from sys.tables where name = 'foo'); +-------------------------------------+ | default | +=====================================+ | next value for "sys"."foo_sequence" | +-------------------------------------+ 1 tuple