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

User · Accepted Answer

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

User · Answer

Even this is also one of the way we can use it  select   from product where 1    1

User · Answer

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

User · Answer

That information is stored in the ALL TAB COLUMNS system table   SQL gt  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 gt  desc dual Name                               Null     Type ----------------------------------------------------- -------- ---------------------- ------------- DUMMY                               VARCHAR2 1

User · Answer

That information is stored in the ALL TAB COLUMNS system table   SQL gt  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 gt  desc dual Name                               Null     Type ----------------------------------------------------- -------- ---------------------- ------------- DUMMY                               VARCHAR2 1

User · Answer

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

User · Answer

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

User · Answer

SELECT COLUMN NAME  all columns   FROM INFORMATION SCHEMA COLUMNS  WHERE TABLE NAME  user

User · Answer

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

User · Answer

SELECT COLUMN NAME  all columns   FROM INFORMATION SCHEMA COLUMNS  WHERE TABLE NAME  user

User · Answer

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

User · Answer

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 · Answer

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

User · Answer

SELECT COLUMN NAME  FROM YourDatabase INFORMATION SCHEMA COLUMNS  WHERE TABLE NAME    YourTableName

User · Answer

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   gt  0        while   row   mysql fetch assoc  result              fields      row  Field

User · Answer

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 · Answer

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

User · Answer

For MySQL  use   SELECT column name  FROM information schema columns  WHERE  table schema    Schema  AND table name    Table Name

User · Answer

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

User · Answer

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

User · Answer

describe YOUR TABLE    In your case    describe EVENT LOG

User · Answer

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

User · Answer

SELECT COLUMN NAME  FROM YourDatabase INFORMATION SCHEMA COLUMNS  WHERE TABLE NAME    YourTableName

User · Answer

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

User · Answer

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

User · Answer

select column name   from information schema columns  where table name    YourTableName  order by ordinal position

User · Answer

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

User · Answer

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

User · Answer

That information is stored in the ALL TAB COLUMNS system table   SQL gt  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 gt  desc dual Name                               Null     Type ----------------------------------------------------- -------- ---------------------- ------------- DUMMY                               VARCHAR2 1

User · Answer

For Oracle  SELECT column name FROM user tab cols WHERE table name UPPER  tableName

User · Answer

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

User · Answer

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   gt  0        while   row   mysql fetch assoc  result              fields      row  Field

User · Answer

Just select first row from the table   for oracle   select   from  lt table name gt  where rownum   1

User · Answer

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

User · Answer

For MySQL  use   SELECT column name  FROM information schema columns  WHERE  table schema    Schema  AND table name    Table Name

User · Answer

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

User · Answer

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

User · Answer

For SQL Server   SELECT  name  AS  Column Name  FROM syscolumns WHERE id   object id  TABLE NAME

User · Answer

Even this is also one of the way we can use it  select   from product where 1    1

User · Answer

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

User · Answer

For SQL Server   SELECT  name  AS  Column Name  FROM syscolumns WHERE id   object id  TABLE NAME

User · Answer

describe YOUR TABLE    In your case    describe EVENT LOG

User · Answer

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 · Answer

select column name   from information schema columns  where table name    YourTableName  order by ordinal position

User · Answer

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 · Answer

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

User · Answer

Just select first row from the table   for oracle   select   from  lt table name gt  where rownum   1

User · Answer

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

User · Answer

That information is stored in the ALL TAB COLUMNS system table   SQL gt  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 gt  desc dual Name                               Null     Type ----------------------------------------------------- -------- ---------------------- ------------- DUMMY                               VARCHAR2 1

User · Answer

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 · Answer

You could also try this  but it might be more information than you need   sp columns TABLE NAME

User · Answer

For Oracle  SELECT column name FROM user tab cols WHERE table name UPPER  tableName

User · Answer

You could also try this  but it might be more information than you need   sp columns TABLE NAME

User · Answer

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

User · Answer

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 · Answer

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 · Answer

You can do this   describe EVENT LOG   or  desc EVENT LOG   Note  only applicable if you know the table name and specifically for Oracle

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

Examples related to sql

Examples related to database

Examples related to oracle