Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using ALL_TAB_COLUMNS to list out columns in a table to looks for dups

Tater_SaladSep 10 2018 — edited Sep 10 2018

Hi,

Oracle version 12c

I want to use the ALL_TAB_COLUMNS

table to list out each column of a table to look for dups in a table for each column.

So your standard dup finding query looks, of course, like...

Select count(*) total, a.col_1

from  ( select * from tableA ) a
group by a.col_1

having count (*) > 1;

I want to leverage the ALL_TAB_COLUMNS table so I can sort of loop through each column name for tableA and

somehow substitute the value of a.col1 with a place holder for each column value so each row of the output table would show

a different columns name and the total number of dups for every value of that column.

I think I did something like this years ago but can't remember what I did.

Has anyone else ever doe this?

If so do you remember how this is done?

The output would look like this

Col1      '# of dup Rows for Col1'

Col2      '# of dup Rows for Col2'

-

-

-

ColX      '# of dup Rows for ColX'

Thanks,

-Tater

This post has been answered by Frank Kulash on Sep 10 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 8 2018
Added on Sep 10 2018
10 comments
1,315 views