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?
Definitely. That seems like a miss.
...had to resort to SQL
Can we expand this beyond identity columns to include any sequence.nextval that is used as a column default value?
Yeah, I was thinking the same thing.
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.
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#;
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.