How do I list all the columns in a table?

Question

For the various popular database systems, how do you list all the columns in a table?

Here is the response https://stackoverflow.com/questions/8739203/oracle-query-to-fetch-column-names/8739400 — KaderLAB, Feb 15 '21 at 12:45

score 350 · Answer 1 · edited Dec 13 '18 at 14:30

350

For MySQL, use:

DESCRIBE name_of_table;

This also works for Oracle as long as you are using SQL*Plus, or Oracle's SQL Developer.

edited Dec 13 '18 at 14:30

walen

7,103
2
37
58

answered Oct 16 '09 at 21:12

dave

12,406
10
42
59

27

This solution is for MYSQL not MSSQL – TheTechGuy Jul 18 '13 at 12:14
2

@dmvianna I don't think that necessarily applies to all of Oracle, but to SQL*Plus. – Tripp Kinetics Sep 18 '14 at 15:48
It should be `DESCRIBE `name_of_table``; – beahacker Sep 21 '18 at 09:25
2

for sqlite - use: pragma table_info(table_name) i.e. sqlite> pragma table_info(column1); – GyRo Oct 21 '18 at 10:33
Editing, since `DESCRIBE` is not an Oracle PLSQL instruction but [a SQL*Plus command](https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12019.htm), and as such it doesn't work in most SQL IDEs. – walen Dec 13 '18 at 14:29

score 154 · Answer 2 · answered Oct 16 '09 at 21:11

154

For Oracle (PL/SQL)

SELECT column_name
FROM user_tab_cols
WHERE table_name = 'myTableName'

For MySQL

SHOW COLUMNS FROM table_name

answered Oct 16 '09 at 21:11

MattGrommes

11,974
9
37
40

5

Youd probably want to order the Oracle query by column_id – David Aldridge Oct 18 '09 at 12:09
8

For Oracle is valid also `DESCRIBE name_of_table`. – Pigueiras Oct 29 '13 at 09:42
use ; show columns in like '%'; Will let you list only the columns starting with the prefix specified. Omitting the angle brackets of course. – rstackhouse Apr 10 '14 at 21:10
1

what is `user_tab_cols` in your query? – Jogi Apr 27 '16 at 11:54
@Jogi - Google "oracle user_tab_cols" - its built-in to Oracle db. – ToolmakerSteve Feb 22 '19 at 00:11
Note that it is possible for Oracle to see a column like `SYS_NC00064$` appear in your result. When you create an index on a table, the database automatically adds system-generated columns to the table to support indexing and searching. These columns are preceded by SYS_NC followed by a numeric value. You could filter those out by adding `AND column_name not like 'SYS%'` to the query. – Jacob van Lingen May 23 '23 at 08:38

score 154 · Answer 3 · edited Apr 27 '22 at 05:58

154

For MS SQL Server:

select COLUMN_NAME from information_schema.columns where table_name = 'tableName'

edited Apr 27 '22 at 05:58

Smart Manoj

5,230
4
34
59

answered Oct 16 '09 at 21:17

Jeff Meatball Yang

37,839
27
91
125

9

The column of interest here would be COLUMN_NAME. – Buggieboy Mar 27 '13 at 19:53
4

This should work on many DBMSs. `information_schema.columns` system view is part of `ANSI SQL` standard ([link](http://en.wikipedia.org/wiki/Information_schema)). – Bogdan Sahlean Jul 28 '13 at 20:12
7

good answer but to avoid duplicates I would use: `select COLUMN_NAME from information_schema.columns where table_name = 'tableName' and table_schema = 'databaseName'` – But those new buttons though.. Feb 25 '15 at 15:14
This is SQL-92 ANSI compliant, and ought to work in all database engines. – Gareth Davidson May 13 '20 at 19:01

score 62 · Answer 4 · edited Apr 16 '16 at 19:02

62

(5 years laters, for the Honor of PostgreSQL, the most advanced DDBB of the Kingdom)

In PostgreSQL:

\d table_name

Or, using SQL:

select column_name, data_type, character_maximum_length
    from INFORMATION_SCHEMA.COLUMNS 
    where table_name = 'table_name';

edited Apr 16 '16 at 19:02

Jamie Schembri

6,047
4
25
37

answered May 08 '15 at 13:20

earizon

2,099
19
29

4

should be \d table_name. \dt table_name lists the relations. – l85m Sep 09 '15 at 20:23
The second query also works for ms sql. I used it because it added two more important variables that I wanted over the answer for [ms sql](https://stackoverflow.com/a/1580487/7576836). – Trevor Vance Mar 14 '23 at 20:07

score 42 · Answer 5 · edited Feb 22 '18 at 16:56

42

I know it's late but I use this command for Oracle:

select column_name,data_type,data_length from all_tab_columns where TABLE_NAME = 'xxxx' AND OWNER ='xxxxxxxxxx'

edited Feb 22 '18 at 16:56

Positive Navid

2,481
2
27
41

answered Nov 17 '14 at 16:25

ka_lin

9,329
6
35
56

http://stackoverflow.com/questions/8739203/oracle-query-to-fetch-column-names/8739400#8739400 – zloctb Aug 18 '15 at 07:09
I tried this in Oracle and it didn't work. The column_name was printed but nothing else. I had to use SELECT CAST(COLUMN_NAME AS CHAR(40)) || ' ' || DATA_TYPE to get a nice format and obtain multiple columns with concatenation. – Eamonn Kenny Apr 25 '19 at 11:29
if query returns empty, try using upper, like here: https://stackoverflow.com/a/17364929/5691498 – Yogev Levy Nov 29 '21 at 14:19

score 32 · Answer 6 · edited Apr 24 '18 at 18:56

32

SQL Server

SELECT 
    c.name 
FROM
    sys.objects o
INNER JOIN
    sys.columns c
ON
    c.object_id = o.object_id
AND o.name = 'Table_Name'

or

SELECT 
    COLUMN_NAME 
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_NAME  = 'Table_Name'

The second way is an ANSI standard and therefore should work on all ANSI compliant databases.

edited Apr 24 '18 at 18:56

Swatantra Kumar

1,324
5
24
32

answered Oct 16 '09 at 21:19

Russ Cam

124,184
33
204
266

1

Neither of these work as written (or at least implied, as I read it) for MS SQL Server. In both cases the table name column stores the name _without_ any `[` `]` around it, so the query must not use them, only the plain table name. If that was not the OP's intention, at least be aware of this. – JonBrave Jul 20 '16 at 11:03
1

@JonBrave - that's correct, the square brackets were there to imply _"insert your table name here"_ :) – Russ Cam Jul 20 '16 at 11:22
Being square brackets, I read it as the "_insert your table name inside square brackets (because of potentially reserved word) here_", and then got no matches :) Perhaps BNF `` would have avoided the ambiguity. Anyway, I realised you might have intended that as I wrote the comment --- it does no harm to warn others just in case.
– JonBrave Jul 20 '16 at 12:24
1

Only works for MSSQL if there is no '[ ]' and the quotes ' ' are needed around the table name. – XValidated Jan 04 '18 at 21:21

score 21 · Answer 7 · edited Apr 11 '21 at 07:34

21

Call below code in MS SQL Server:

sp_columns [tablename]

edited Apr 11 '21 at 07:34

Amin Golmahalleh

3,585
2
23
36

answered Oct 16 '09 at 21:14

Bryan

8,748
7
41
62

score 13 · Answer 8 · edited May 13 '16 at 01:05

13

Microsoft SQL Server Management Studio 2008 R2:

In a query editor, if you highlight the text of table name (ex dbo.MyTable) and hit ALT+F1, you'll get a list of column names, type, length, etc.

ALT+F1 while you've highlighted dbo.MyTable is the equivalent of running EXEC sp_help 'dbo.MyTable' according to this site

I can't get the variations on querying INFORMATION_SCHEMA.COLUMNS to work, so I use this instead.

edited May 13 '16 at 01:05

Nate Anderson

18,334
18
100
135

answered May 03 '13 at 22:23

Leslie Sage

391
2
8

1

Did not work in SSMS 2012. Btw did you mean SQL Server Management Studio 2008? – TheTechGuy Jul 18 '13 at 12:19
1

Yep, more precisely I meant Microsoft SQL Server Management Studio 2008 R2. I'll edit. – Leslie Sage Aug 12 '13 at 02:50

Prahalad Gaggar · Answer 9 · 2021-05-05T04:52:02.333

7

For SQL Server

sp_help tablename

edited May 05 '21 at 04:52

answered Aug 12 '13 at 03:13

Prahalad Gaggar

11,389
16
53
71

Aaron Bertrand · Answer 10 · 2022-10-28T14:56:29.563

5

Just a slight correction on the others in SQL Server (schema prefix is becoming more important!):

SELECT name
  FROM sys.columns 
  WHERE [object_id] = OBJECT_ID(N'dbo.tablename');

edited Oct 28 '22 at 14:56

answered Oct 16 '09 at 22:59

Aaron Bertrand

272,866
37
466
490

Aaron, Thanks for adding this option to the list. Previously I was using this code. SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table_Name' However, it doesn’t always work. Especially on very large tables, like 50 million rows or more. Not sure why. Your option works just great on all the tables I have tried so far. Thanks, and kudos. – Aubrey Love Oct 28 '22 at 14:52

Mircea Grelus · Answer 11 · 2009-10-16T21:41:42.770

3

SQL Server

To list all the user defined tables of a database:

use [databasename]
select name from sysobjects where type = 'u'

To list all the columns of a table:

use [databasename]
select name from syscolumns where id=object_id('tablename')

edited Oct 16 '09 at 21:41

answered Oct 16 '09 at 21:20

Mircea Grelus

2,905
1
20
14

Heh? This is just wrong...you can only use USE for databases...And the query returns all user defined tables in the database, which is not what the OP wanted. – Maximilian Mayerl Oct 16 '09 at 21:25

score 3 · Answer 12 · edited Feb 27 '15 at 14:00

3

Example:

select Table_name as [Table] , column_name as [Column] , Table_catalog as [Database], table_schema as [Schema]  from information_schema.columns
where table_schema = 'dbo'
order by Table_name,COLUMN_NAME

Just my code

edited Feb 27 '15 at 14:00

mmmmmpie

2,908
1
18
26

answered Feb 27 '15 at 13:37

Matthew lowe

31
1

score 0 · Answer 13 · answered Jun 28 '23 at 11:09

0

AWS Athena

To list all columns from a table you can use:

SHOW COLUMNS {FROM|IN} database_name.table_name

or

SHOW COLUMNS {FROM|IN} table_name [{FROM|IN} database_name]

The FROM and IN keywords can be used interchangeably.

AWS Athena doc

answered Jun 28 '23 at 11:09

mmsilviu

1,211
15
25

How do I list all the columns in a table?

13 Answers13

Linked

Related