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.

The below query worked for me in Oracle database.

select COLUMN_NAME from ALL_TAB_COLUMNS where TABLE_NAME='MyTableName';

in oracle you can use

desc users

to display all columns containing in users table

You may try this : ( It works on 11g and it returns all column name from a table , here test_tbl is the table name and user_tab_columns are user permitted table's columns )

select  COLUMN_NAME  from user_tab_columns
where table_name='test_tbl';

the point is that in toad u have to write table name capital, like this:

select *
FROM all_tab_columns
where table_name like 'IDECLARATION';

Oracle query to fetch column names

Related

Recent Posts