Sequence -table -columsn

dba_udb · #1 (**permalink**) 10-26-05, 09:13

v 8.2 OS AIX/WIN

HOw do i find out the table/column that is using a sequence.
In one of my database i see that few tables are using sequences.
I know the sequences names . But i need to find out the tables which are using those sequences..

thanks

Wim · #2 (**permalink**) 10-26-05, 10:26

Quote:

But i need to find out the tables which are using those sequences.

You can't. Sequences are controlled by the program(s) that operate(s) on your database.

A sequence is a very flexible thing. You could define one sequence and use it to generate unique ID's for all the tabels in your database thereby giving each object (record) a unique ID, not only within its table but within the entire database.
I don't think this is common practice.

Code:

CREATE TABLE FOO (
	ID INTEGER NOT NULL,
	...
	CONSTRAINT PK_FOO PRIMARY KEY (ID)
	);

CREATE SEQUENCE MYSEQ START WITH 100;

The program could use MYSEQ to generate the PK for FOO, and there is no way you can find this connection by looking at the DDL scripts.

What you could do by trial and error is to get the next sequence (*) from MYSEQ and compare that to the most recent ID's of the tables you suspect are controlled by that sequence. If the values are close, there is a possibility they are connected. You should then create a new record using the program and verifu that the sequence is altered and that the PK of the record matches the sequence value (*).

przytula · #3 (**permalink**) 10-26-05, 12:07

for any other object I know that sysibm.sysdependencies lists objects that depend on other objects of any kind...

Wim · #4 (**permalink**) 10-27-05, 04:25

Quote:

What you could do by trial and error is to get the next sequence (*) from MYSEQ and compare that to the most recent ID's of the tables you suspect are controlled by that sequence. If the values are close, there is a possibility they are connected. You should then create a new record using the program and verifu that the sequence is altered and that the PK of the record matches the sequence value (*).

This isn't foolproof. In one of our programs we use a sequence to generate a unique number and then add the MOD(97) to it at the end. eg. with NEXTVAL == 1000 and MOD(1000, 97) == 30, the resulting number would be 100030.
So there is no guarantee that the numbers generated by the sequence are used without modification. There is also no guarantee that the values are only used in PK fields and the CACHE switch can add additional complexity to your search.

That being said, sequences are almost always used for the PK of one table.

The best way to go is to ask it to the developers of the program(s).

Internet Services

Web Development

Digital Marketing

Consumer Tech