There are 2 selects that select all tables and all columns of these tables for the selected schema. For columns, the type and name of the column is displayed. For tables, just TABLE .

 SELECT table_name, column_name, CASE data_type WHEN 'NUMBER' THEN 'NUM' WHEN 'VARCHAR2' THEN 'VAR2' WHEN 'DATE' THEN 'DATE' END as type FROM user_tab_columns UNION ALL SELECT DISTINCT table_name, 'TABLE', 'TABLE' FROM user_tab_columns;

Is it possible to somehow do the same thing through one SELECT without UNION ?

 ++++++++++++++++++++++++++++++++++++ | table_name | column_name | type | ++++++++++++++++++++++++++++++++++++ | ORDER | ID | NUM | | ORDER | PRICE | NUM | | ORDER | DATE | DATE | | ORDER | TABLE | TABLE | | .... | .... | ... | ++++++++++++++++++++++++++++++++++++

    2 answers 2

    Rows for a table can be viewed as rows of totals supplied under the data for each table, there’s nothing to summarize, but it doesn’t matter. The ROLLUP operator creates a row of totals in the section we need, but it is not necessary to summarize anything:

     SELECT table_name, nvl(column_name,'TABLE'), decode(data_type,NULL,'TABLE','NUMBER','NUM','VARCHAR2','VAR2',data_type) as type FROM user_tab_columns GROUP BY ROLLUP(table_name,(column_name,data_type)) HAVING table_name is not null

    Since we need a total for each table, the first ROLLUP parameter we give the name of the table. We do not need totals for columns and data types, therefore these two fields are in separate brackets, the second parameter. But rollup is trying to summarize the total sample for the whole sample, it is distinguished by the fact that the table_name column will be NULL for it, in the having condition we deselect this line.

    • Thank. Exactly what is needed. - compl

    I think you can use the hierarchical query

     select distinct table_name, decode(lvl, 1, column_name, 'TABLE') column_name, decode(lvl, 1, decode(data_type, 'NUMBER', 'NUM', 'VARCHAR2', 'VAR2', data_type), 'TABLE') type from user_tab_columns s cross join (select level lvl from dual connect by level <= 2) where s.table_name = 'EMPLOYEES';
    • Thanks, quite an interesting way through recursive queries. - compl
    • @compl This way and without recursive would work, he needs to multiply the lines. recursion after the fact makes select 1 lvl from dual union all select 2 from dual . And personally, instead of cross, I would add the connection condition s.column_id=1 or lvl=1 then only the first column of each table would multiply and the distinct would not be needed, which might even give some ROLLUP speed gain - Mike