Identify table associated system generated identity sequence

Toad for Oracle - Beta
1

I had to resort to SQL to find the table associated with a system generated identity sequence. As a feature request in the object browser, would it be feasible to use the "used by" column to display the table name for these ISEQ$$_ generated sequences?

2

Definitely. That seems like a miss.

...had to resort to SQL
image

3

John,

Can we expand this beyond identity columns to include any sequence.nextval that is used as a column default value?

Doug

4

Yeah, I was thinking the same thing.

5

Ok, @DougK , I give. How did you manage to do this via SQL? The DATA_DEFAULT column is LONG so I can't search it in a WHERE clause. I could limit my query to NUMBERs where DEFAULT_LENGTH is not null, fetch all of those and search client side, but....yuck.

I guess could write a pl/sql block to do it. Slightly less yuck.

6

This works for system generated identity sequences. I don't have a good solution for DEFAULT sequence.nextval assignments.

select  o.object_name table_name, c.column_name ,s.object_name sequence_name
 from  user_objects s,
       user_objects o,
       user_tab_columns c,
       sys.idnseq$ i
 where c.table_name = o.object_name
   and i.obj# = o.object_id
  and i.intcol# = c.column_id
  and s.object_id = i.seqobj#;
7

Ah. Thanks. I didn't consider using a SYS table. I'll need to find a solution will work for users users without that level of access. I'll probably write a PL/SQL block. It's a little bit of a pain, but it'll work.