Oracle Query to Fetch Column Names

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 name in addition to the result (oracle)

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

var reader = cmd.ExecuteReader();

var columns = new List<string>();

for(int i=0;i<reader.FieldCount;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