Friday, October 16, 2015

Oracle SQL to show all columns for all tables in particular schema

Oracle SQL to show all columns for all tables in particular schema:

SELECT   table_name, SUBSTR (MAX (all_columns), 2) all_columns
       FROM (SELECT     table_name,
                        SYS_CONNECT_BY_PATH (column_name, ',') all_columns
                   FROM (SELECT table_name, column_name,
                                ROW_NUMBER () OVER (PARTITION BY table_name ORDER BY column_id)
                                                                    column_no
                           FROM all_tab_columns c
                          WHERE c.owner = 'YOUR_SCHEMA'
                            --AND column_name NOT IN ('OLD_', 'DEP_')
                            )
             CONNECT BY PRIOR table_name = table_name
                    AND PRIOR column_no = column_no - 1
             START WITH column_no = 1)
   GROUP BY table_name;


output is like:
TABLE_NAME | ALL_COLUMNS
------------------------------------------------- 
FLAGS            | ID,VALUE,LASTUPD
MESSAGES    | DB_TIMESTAMP,PRIORITY,MESSAGE,DATASETS,IDS,CODE

No comments:

Post a Comment