Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005
    Posts
    73

    Unanswered: Sequence -table -columsn

    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

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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 (*).
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2004
    Posts
    374

    depend

    for any other object I know that sysibm.sysdependencies lists objects that depend on other objects of any kind...
    Best Regards, Guy Przytula
    DB2/ORA/SQL Services
    DB2 DBA & Advanced DBA Certified
    DB2 Dprop Certified
    http://users.skynet.be/przytula/dbss.html

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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).
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •