How can I get column names from a table?

Question

I need to query the database to get the column/field 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.

Please help? Thanks in advance.

Answer 1

You can do this:

describe EVENT_LOG;

or

desc EVENT_LOG;

Answer 2

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)

Answer 3

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'

Answer 4

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.

Answer 5

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