Get Columns Names of Table

Question

How I can Get for a specific Table its columns Names ? I tried this :

SELECT  column_name
FROM USER_TAB_COLUMNS
WHERE table_name = 'x' ;

But it doesn't work.

Answer 1

Try this :

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'TABLENAME'

Hope this helps.

EDIT :

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.

Source : Oracle query to fetch column names

Answer 2

String comparisons in Oracle are case sensitive by default, and table and column names are upper case by default, so you need to make sure that the capitalization of the table name you are searching for matches the the way it's stored in the database, so unless your table was named with mixed case or all lower case try making sure your string is all upper case.

SELECT column_name from USER_TAB_COLUMNS
 WHERE table_name = 'X'; -- not 'x'

Additionally, if you don't own the table, then you need to use either ALL_TAB_COLUMNS or DBA_TAB_COLUMNS instead of USER_TAB_COLUMNS since USER_TAB_COLUMNS only lists details for tables owned by your current schema.

Answer 3

You can just describe the table:

desc x;

Answer 4

Try to queryUSER_TAB_COLUMNS view

SELECT  column_name
FROM USER_TAB_COLUMNS
WHERE table_name = 'TABLE_NAME'