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

Question

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:

• Microsoft SQL Server
• MySQL
• PostgreSQL

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

Answer 1

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 2

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

Answer 3

You can do this:

describe EVENT_LOG

or

desc EVENT_LOG

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

Answer 4

For SQL Server 2008, we can use information_schema.columns for getting column information

SELECT *
FROM   information_schema.columns
WHERE  table_name = 'Table_Name'
ORDER  BY ordinal_position  

Answer 5

For SQLite I believe you can use something like the following:

PRAGMA table_info(table-name);

Explanation from sqlite.org:

This pragma returns one row for each column in the named table. Columns in the result set include the column name, data type, whether or not the column can be NULL, and the default value for the column. The "pk" column in the result set is zero for columns that are not part of the primary key, and is the index of the column in the primary key for columns that are part of the primary key.

See also: Sqlite.org Pragma Table Info

Answer 6

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 7

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 8

For Oracle

SELECT column_name FROM user_tab_cols WHERE table_name=UPPER('tableName');

Answer 9

describe YOUR_TABLE;

In your case :

describe EVENT_LOG;

Answer 10

Even this is also one of the way we can use it

select * from product where 1 != 1

Answer 11

select column_name,* from information_schema.columns
 where table_name = 'YourTableName'
order by ordinal_position

Answer 12

For MySQL, use

SELECT column_name 
FROM information_schema.columns 
WHERE 
table_schema = 'Schema' AND table_name = 'Table_Name'

Answer 13

For SQL Server:

SELECT [name] AS [Column Name]
FROM syscolumns
WHERE id = object_id('TABLE_NAME')

Answer 14

SELECT COLUMN_NAME 'all_columns' 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='user';

Answer 15

You could also try this, but it might be more information than you need:

sp_columns TABLE_NAME

Answer 16

SELECT A.COLUMN_NAME, A.* FROM all_tab_columns a 
WHERE table_name = 'Your Table Name'
AND A.COLUMN_NAME = 'COLUMN NAME' AND a.owner = 'Schema'

Answer 17

Mysql

SHOW COLUMNS FROM a_table_named_users WHERE Field REGEXP 'user_id|user_name|user_pass'

This will return a result something like this:

Field     |  Type        |   Null   |   Key   |   Default   |   Extra  
user_id      int(8)          NO         PRI       NULL          auto_increment
user_name    varchar(64)     NO         MUL       NULL
user_pass    varchar(64)     NO                   NULL

Then to pull out the values you can simply

fetch row[0]

This is also great for passing input dynamically since the REGEXP needs the '|' for multiple inputs, but is also a way to keeps data separated and easy to store/pass to classes/functions.

Try throwing in dummy data as well for security when sending it out and compare what was returned when receiving any errors.

Answer 18

In Oracle, there is two views that describe columns:

• DBA_TAB_COLUMNS describes the columns of all tables, views, and clusters in the database.

• USER_TAB_COLUMNS describes the columns of the tables, views, and
  clusters owned by the current user. This view does not display the
  OWNER column.

Answer 19

The answer is here: http://php.net/manual/en/function.mysql-list-fields.php I'd use the following code in your case:

$result = mysql_query("SHOW COLUMNS FROM sometable");
if (!$result) {
    echo 'Could not run query: ' . mysql_error();
    exit;
}
$fields = array();
if (mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_assoc($result)) {
        $fields[] = $row['Field'];
    }
}

Answer 20

you can run this query

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%%' --if you want to find specific column write here 
ORDER BY schema_name, table_name;

Answer 21

This will return all tables:

SELECT table_name, column_name, data_type, data_length
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'YOUR_TABLE_NAME'

Answer 22

Try this

select * from sys.all_columns c join sys.objects o on c.object_id=o.object_id where o.name = 'TABLENAME' and c.name like '%COLUMN NAME%'

Answer 23

Just select first row from the table , for oracle : select * from <table name> where rownum = 1;

Answer 24

Came across this question looking for access to column names on Teradata, so I'll add the answer for their 'flavour' of SQL:

SELECT ColumnName
FROM DBC.Columns
WHERE DatabaseName='DBASE_NAME'
AND TableName='TABLE_NAME';

The info is stored in the DBC dbase.

Getting data types is a little bit more involved: Get column type using teradata system tables

Answer 25

I did it like this

SELECT 
    TOP 0
    *
FROM
    Posts

It works even in http://data.stackexchange.com whose service tables I am not aware of!

Answer 26

SELECT COLUMN_NAME 
FROM YourDatabase.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'YourTableName'