How to Get Column Names from a Table in Oracle

Oracle query to fetch column names

The Oracle equivalent for information_schema.COLUMNS is USER_TAB_COLS for tables owned by the current user, ALL_TAB_COLS or DBA_TAB_COLS for tables owned by all users.

Tablespace is not equivalent to a schema, neither do you have to provide the tablespace name.

Providing the schema/username would be of use if you want to query ALL_TAB_COLS or DBA_TAB_COLS for columns OF tables owned by a specific user. in your case, I'd imagine the query would look something like:

String sqlStr= "
SELECT column_name
  FROM all_tab_cols
 WHERE table_name = 'USERS'
   AND owner = '" +_db+ "'
   AND column_name NOT IN ( 'PASSWORD', 'VERSION', 'ID' )"

Note that with this approach, you risk SQL injection.

EDIT: Uppercased the table- and column names as these are typically uppercase in Oracle; they are only lower- or mixed case if created with double quotes around them.

How can I get column names from a table in Oracle?

You can query the USER_TAB_COLUMNS table for table column metadata.

SELECT table_name, column_name, data_type, data_length
FROM USER_TAB_COLUMNS
WHERE table_name = 'MYTABLE'

How to get all columns' names for all the tables in Oracle?

You can use

SELECT *
  FROM user_tab_cols
 ORDER BY table_name, column_name, column_id

after connecting to the schema from which you want to get the related information.

Get column names in order in Oracle DB

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='' 
AND OWNER = ''
ORDER BY COLUMN_ID

get column name in addition to the result (oracle)

You can use reader.GetName(i), for example:

var reader = cmd.ExecuteReader();

var columns = new List();

for(int i=0;i{
   columns.Add(reader.GetName(i));
}

Oracle select column names dynamically from another table

Assuming that the columns have compatible types, then you can do what you want by using case expressions:

select (case when t1.field1 = 'col1' then col1
             when t1.field1 = 'col2' then col2
             when t1.field1 = 'col3' then col3
             when t1.field1 = 'col4' then col4
        end),
       (case when t1.field2 = 'col1' then col1
             when t1.field2 = 'col2' then col2
             when t1.field2 = 'col3' then col3
             when t1.field2 = 'col4' then col4
        end)
from tab2 join
     (select id, min(fieldname) as field1, max(fieldname) as field2
      from t1
      group by id
     ) t1
     on t1.id = t2.id

---

Related Topics