CODEWITHSUNDEEP
sqloracle-database
user2665166
user2665166

Reputation: 471

Get column names in order in Oracle DB

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?

Upvotes: 6

Views: 14767

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60292

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

Upvotes: 3

sstan
sstan

Reputation: 36513

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

Upvotes: 21

Related Questions