Query sequences

After you create a sequence, you can use the SHOW SEQUENCES statement or a view to query the sequence.

Use the SHOW SEQUENCES statement to query sequences

The SHOW SEQUENCES statement allows you to query all sequences created in the current tenant.

Here is a sample statement:

obclient> SHOW SEQUENCES
              [LIKE expression
               | LIKE expression IN database_name
               | IN database_name
               | WHERE sequence_name='sequence_name'
              ];

where:

• LIKE expression specifies the information about the sequence name to be queried.

• IN database_name specifies the database to which the sequence to be queried belongs.

• WHERE sequence_name='sequence_name' specifies the name of the sequence to be queried.

Here are some examples:

• Query all sequences created in the current tenant.

  obclient> SHOW SEQUENCES;
  +-------------------+
  | Sequences_in_test |
  +-------------------+
  | seq1              |
  | meq2              |
  +-------------------+
  2 rows in set
  

• Query all sequences whose name contains s.

  obclient> SHOW SEQUENCES LIKE '%s%';
  +-------------------------+
  | Sequences_in_test (%s%) |
  +-------------------------+
  | seq1                    |
  +-------------------------+
  1 rows in set
  

• Query all sequences in the test database.

  obclient> SHOW SEQUENCES IN test;
  +-------------------+
  | Sequences_in_test |
  +-------------------+
  | seq1              |
  | meq2              |
  +-------------------+
  2 rows in set
  

• Query the sequence named seq1 in the current tenant.

  obclient> SHOW SEQUENCES WHERE sequence_name='seq1';
  +-------------------+
  | Sequences_in_test |
  +-------------------+
  | seq1              |
  +-------------------+
  1 row in set
  

Use a view to query sequences

In a MySQL tenant of OceanBase Database, you can query all sequences by querying the oceanbase.DBA_OB_SEQUENCE_OBJECTS view that displays the sequence information such as the ID, name, start value, increment step, cycling flag, maximum value, and minimum value of the sequence, as well as the database to which the sequence belongs.

A sample query is as follows:

obclient> SELECT * FROM oceanbase.DBA_OB_SEQUENCE_OBJECTS;
+-------------+------------------+-------------+---------------+-----------+-----------+--------------+------------+------------+------------+------------+---------------------+
| SEQUENCE_ID | SCHEMA_VERSION   | DATABASE_ID | SEQUENCE_NAME | MIN_VALUE | MAX_VALUE | INCREMENT_BY | START_WITH | CACHE_SIZE | ORDER_FLAG | CYCLE_FLAG | IS_SYSTEM_GENERATED |
+-------------+------------------+-------------+---------------+-----------+-----------+--------------+------------+------------+------------+------------+---------------------+
|      500003 | 1675748057234736 |      500002 | seq1          |         1 |        10 |            2 |          1 |         30 |          0 |          0 |                   0 |
+-------------+------------------+-------------+---------------+-----------+-----------+--------------+------------+------------+------------+------------+---------------------+
1 row in set

For more information about fields in the oceanbase.DBA_OB_SEQUENCE_OBJECTS view, see oceanbase.DBA_OB_SEQUENCE_OBJECTS.

References

For more information about operations on a sequence, see the following topics:

• Create a sequence

• Reference a sequence

• Modify a sequence

• Drop a sequence