Home » SQL & PL/SQL » SQL & PL/SQL » how to find the sequence
how to find the sequence [message #37110] Fri, 18 January 2002 03:04 Go to next message
Messages: 2
Registered: January 2002
Junior Member
I want to find out from the name of a given table name and primary key column name as to which sequence is populating that particular primary key column
Re: how to find the sequence [message #37112 is a reply to message #37110] Fri, 18 January 2002 04:56 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
there is no way to find out because table column and sequence association is not part of definition of table. normally sequence numbers populated through triggers in table.

If so, you can find out by querying user_triggers/all_triggers

select trigger_body from user_triggers where table_name='TABLE_NAME_HERE';

it displays all triggers code of given table.
Re: how to find the sequence [message #37151 is a reply to message #37112] Mon, 21 January 2002 08:10 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
As Suresh says, there is nothing in the data dictonary to track that. One sequence number could be used to populate the PK's of all the tables in your schema.

Most of the code I've ever used to populate PK's wasn't done in triggers, so there is another way to get a clue:

-- Find the biggest PK in the table...
select pax(pm_col) from my_table;

-- find which sequences are close
-- you won't find an exact match - cache_number throws the actual sequence off from the value in last_number
select sequence_name, cache_size, last_number from user_sequences;
Previous Topic: Time conversion
Next Topic: what is two categories on oracle??
Goto Forum:

Current Time: Mon Aug 02 11:05:58 CDT 2021