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
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