Menu
I am getting all the column names in a table using:
SELECT COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME='<TABLE_NAME>'
AND OWNER = '<SCHEMA>'
I am calling this same sql statment on an identical table in a different database, but getting the results in a different order. Is there something I can append to my sql such that the columns will be returned in the same order?
741
Best AnswerSELECT owner, column_nameFROM all_tab_columnsWHERE table_name = 'YOUR_TABLE_HERE'ORDER BY owner, table_name; You may wnat to add "AND owner =..." as the above query will return all tables/views that have the table_name 'YOUR_TABLE_HERE'. i.e. more than one owner can have a table/view of the same name.
SELECT * FROM INFORMATION_SCHEMA. COLUMNS WHERE COLUMN_NAME LIKE 'PREFIX%' ORDER BY TABLE_NAME; Query: SELECT * FROM INFORMATION_SCHEMA.
You can reorder the columns on a page. To reorder columns: From the menu at the top of the page, select View, and then Reorder Columns.
Assuming your tables really are identical with columns defined in the same order in both databases, you can order by COLUMN_ID to ensure consistent ordering.
SELECT COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME='<TABLE_NAME>'
AND OWNER = '<SCHEMA>'
ORDER BY COLUMN_ID
If you want to guarantee that the columns are listed in the same order regardless of the order in which they were defined, sort by the column name:
...
ORDER BY column_name
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
