CODEWITHSUNDEEP
sqloracle-database
S_raj
S_raj

Reputation: 1

Oracle SQL query help getting all the columns of a list of tables in tabular format

Hi I am trying to get list of all column-names associated with different tables in a tabular format.

Thank you in advance for your help.

The query I have is:

select table_name , COLUMN_NAME
from all_tab_columns 
where column_name in ('COLUMN_1','COLUMN_2','COLUMN_3','COLUMN_4') 
group by table_name, column_name;

What I would like to see is:

TABLE_NAME COLUMN_NAME COLUMN_NAME COLUMN_NAME
Table_a Column_1 Column_2 Column_3
Table_b Column_1 Column_2 Column_3
Table_c Column_1 Column_2 Column_3

Upvotes: 0

Views: 43

Answers (2)

MT0
MT0

Reputation: 167972

You can PIVOT:

SELECT *
FROM   (
         SELECT table_name , COLUMN_NAME
         FROM   all_tab_columns 
         WHERE  column_name in ('COLUMN_1','COLUMN_2','COLUMN_3','COLUMN_4')
       )
       PIVOT (
         MAX(column_name) FOR column_name IN (
           'COLUMN_1' AS column_name_1,
           'COLUMN_2' AS column_name_2,
           'COLUMN_3' AS column_name_3,
           'COLUMN_4' AS column_name_4
         )
       );

Which, for the sample data:

CREATE TABLE table_1 (Column_1, Column_2, Column_3, Column_4 ) AS
  SELECT 1, 1, 1, 1 FROM DUAL;

CREATE TABLE table_2 (Column_1, Column_2, Column_3, Column_4 ) AS
  SELECT 1, 1, 1, 1 FROM DUAL;

CREATE TABLE table_3 (Column_4, Column_3, Column_2, Column_1 ) AS
  SELECT 1, 1, 1, 1 FROM DUAL;

Outputs:

TABLE_NAME COLUMN_NAME_1 COLUMN_NAME_2 COLUMN_NAME_3 COLUMN_NAME_4
TABLE_1 COLUMN_1 COLUMN_2 COLUMN_3 COLUMN_4
TABLE_2 COLUMN_1 COLUMN_2 COLUMN_3 COLUMN_4
TABLE_3 COLUMN_1 COLUMN_2 COLUMN_3 COLUMN_4

Alternatively if you want the first 4 columns (in the order they occur in the table) for all tables that that contain at least one of those columns then you can use:

SELECT owner,
       table_name,
       column_name_1,
       column_name_2,
       column_name_3,
       column_name_4
FROM   (
         SELECT owner,
                table_name,
                COLUMN_NAME,
                column_id,
                COUNT(
                  CASE
                  WHEN column_name in ('COLUMN_1','COLUMN_2','COLUMN_3','COLUMN_4')
                  THEN 1
                  END
                ) OVER (PARTITION BY owner, table_name) AS has_matches
         FROM   all_tab_columns
       )
       PIVOT (
         MAX(column_name) FOR column_id IN (
           1 AS column_name_1,
           2 AS column_name_2,
           3 AS column_name_3,
           4 AS column_name_4
         )
       )
WHERE  has_matches > 0;

Outputs:

OWNER TABLE_NAME COLUMN_NAME_1 COLUMN_NAME_2 COLUMN_NAME_3 COLUMN_NAME_4
FIDDLE_MGOBEHWSSMRSDSPNPYSW TABLE_1 COLUMN_1 COLUMN_2 COLUMN_3 COLUMN_4
FIDDLE_MGOBEHWSSMRSDSPNPYSW TABLE_2 COLUMN_1 COLUMN_2 COLUMN_3 COLUMN_4
FIDDLE_MGOBEHWSSMRSDSPNPYSW TABLE_3 COLUMN_4 COLUMN_3 COLUMN_2 COLUMN_1

fiddle

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

That looks like some kind of pivoting.

Sample tables whose columns you'd like to see:

SQL> select * from emp where rownum = 1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

SQL> select * from dept where rownum = 1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

Query itself:

SQL> select *
  2  from (select table_name, column_name, column_id
  3        from user_Tab_columns
  4        where table_name in ('DEPT', 'EMP')
  5       )
  6  pivot
  7  (max(column_name)
  8   for column_id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
  9  )
 10  order by table_name;

TABLE_NAME 1          2          3          4          5          6          7          8          9          10
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
DEPT       DEPTNO     DNAME      LOC
EMP        EMPNO      ENAME      JOB        MGR        HIREDATE   SAL        COMM       DEPTNO

SQL>

Upvotes: 0

Related Questions