How to select all possible values of columns from all tables?

Question

SELECT POM.TABLE_NAME, POM.COLUMN_NAME
FROM ALL_TAB_COLUMNS POM
WHERE  POM.COLUMN_NAME LIKE'%STATUS%'

I want to see all possible values in columns on the list(in one row if possible). How can i modify this select to do it?

i want soemthing like this

 TABLE_NAME | COLUMN_NAME |VALUES
 -----------| ----------- | -------
 CAR        | COLOR       | RED,GREEN

Can you please provide sample data to illustrate what you're trying to achieve? I'm a bit unclear as to what *all possible values in columns on the list* means. — Siyual, Sep 22 '16 at 13:44
You can't. You are querying metadata tables and these don't have information about the actual data. Doing what you want requires dynamic SQL (i.e. `execute`). — Gordon Linoff, Sep 22 '16 at 13:44
[See this question and its answers as a guide to producing a comma-separated list of values](http://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle). The "all columns in all tables" thing is going to require a lot of work with dynamic SQL. Best of luck. — Bob Jarvis - Слава Україні, Sep 22 '16 at 14:19
WHY? Why do you want to do this? I can't think of any legitimate business purpose for this. If it is for practice, then what are you trying to practice? String aggregation? You don't need "all columns in all tables" for that. Perhaps you are specifically trying to practice dynamic SQL? That would make more sense; but doing this for ALL columns in ALL tables still seems extreme. — , Sep 22 '16 at 14:53
I could see something like this being useful if you are handed a big database with no documentation and need to get your bearings quickly. "All possible values" isn't practical, of course (think primary key on 100 million row table), but something that spat out, say, the 1st 10 distinct values of each column could be helpful. — Matthew McPeak, Sep 22 '16 at 15:53

score 0 · Answer 1 · answered Sep 23 '16 at 11:06

You can use the below query for your requirement. It fetched distinct column values for a table. It can be used only for the table having limited number of distinct values as I have used LISTAGG function.

SELECT POM.TABLE_NAME, POM.COLUMN_NAME,
       XMLTYPE(DBMS_XMLGEN.GETXML('SELECT LISTAGG(COLUMN_NAME,'','') WITHIN GROUP (ORDER BY COLUMN_NAME) VAL 
                                   FROM (SELECT DISTINCT '|| POM.COLUMN_NAME ||' COLUMN_NAME
                                         FROM '||POM.OWNER||'.'||POM.TABLE_NAME||')')
              ).EXTRACT('/ROWSET/ROW/VAL/text()').GETSTRINGVAL() VAL
FROM   ALL_TAB_COLUMNS POM
WHERE  POM.COLUMN_NAME LIKE'%STATUS%';

How to select all possible values of columns from all tables?

1 Answers1