How do I know which column applies a sequence?

Question

I need to get all the sequences with their table name along with the name of the column in which the sequence is applied. How did I manage to get the table name that matches the sequence because my database stores the name with the name in the form of the table name from the data dictionary (all_sequences and all_tables).

Please let me know how to get the corresponding column name if possible!

Answer 1

In Oracle, a sequence is an independent object; it is not associated with a specific table or column. For example, you can run this query to get a list of sequences:

SELECT * FROM all_sequences

And when you create the sequence, you will notice that there is nothing in the CREATE SEQUENCE syntax to indicate that you want to associate it with a table or column.

A sequence is just a unique number generator, it doesn’t care what you do with the number generated from it (i.e. whether you paste the value of the sequence into a table, etc.), it is just there to provide this unique number.

Thus, it is not possible to determine for a given column which sequence was used (if any) to create this column value.

Answer 2

"" , LAST_NUMBER all_sequences SQL ( , ).

select table_name, column_name, utl_raw.cast_to_number(high_value) 
from dba_tab_columns
where owner = '...'
and data_type = 'NUMBER'
and (owner, table_name, column_name) in 
  (select cc.owner, cc.table_name, cc.column_name
  from dba_cons_columns cc 
     join dba_constraints c 
       on cc.owner = c.owner and cc.constraint_name = c.constraint_name
  where c.constraint_type = 'P')
order by 3;

, (, , _ _SEQ ).

Answer 3

See dcp answer.

However, the sequence will usually be used to create a unique key for the table to which it corresponds - try to find primary keys and / or unique indexes in the corresponding table.