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

Paxenos_54844 0
Viewed 808280

I need to query the database to get the column names, not to be confused with data in the table. For example, if I have a table named EVENT_LOG that contains eventID, eventType, eventDesc, and eventTime, then I would want to retrieve those field names from the query and nothing else.

I found how to do this in:

But I need to know: how can this be done in Oracle?

oracle sql database
25 Answers

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'
Dennis_Myren_300520

In SQL Server...

SELECT [name] AS [Column Name]
FROM syscolumns
WHERE id = (SELECT id FROM sysobjects WHERE type = 'V' AND [Name] = 'Your table name')

Type = 'V' for views Type = 'U' for tables

user_484780

You can do this:

describe EVENT_LOG

or

desc EVENT_LOG

Note: only applicable if you know the table name and specifically for Oracle.

user_265920

That information is stored in the ALL_TAB_COLUMNS system table:

SQL> select column_name from all_tab_columns where table_name = 'DUAL';

DUMMY

Or you could DESCRIBE the table if you are using SQL*PLUS:

SQL> desc dual
Name                               Null?    Type
----------------------------------------------------- -------- ---------------------- -------------
DUMMY                               VARCHAR2(1)
Jon_Ericson_14380

The other answers sufficiently answer the question, but I thought I would share some additional information. Others describe the "DESCRIBE table" syntax in order to get the table information. If you want to get the information in the same format, but without using DESCRIBE, you could do:

SELECT column_name as COLUMN_NAME, nullable || '       ' as BE_NULL,
  SUBSTR(data_type || '(' || data_length || ')', 0, 10) as TYPE
 FROM all_tab_columns WHERE table_name = 'TABLENAME';

Probably doesn't matter much, but I wrote it up earlier and it seems to fit.

Sasha_557450
Related