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 329 · Answer 1 · edited Dec 13 '18 at 14:30

329

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

6,578
2
33
54

answered Oct 16 '09 at 21:12

dave

11,952
9
40
58

21

This solution is for MYSQL not MSSQL – Hammad Khan 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 145 · Answer 2 · answered Oct 16 '09 at 21:11

145

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,584
9
35
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
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

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

128

For MS SQL Server:

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

edited Apr 27 '22 at 05:58

Smart Manoj

4,375
4
27
51

answered Oct 16 '09 at 21:17

Jeff Meatball Yang

35,999
26
90
121

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
6

good answer but to avoid duplicates I would use: `select COLUMN_NAME from information_schema.columns where table_name = 'tableName' and table_schema = 'databaseName'` – Eaten by a Grue 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 54 · Answer 4 · edited Apr 16 '16 at 19:02

54

(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,007
4
24
36

answered May 08 '15 at 13:20

earizon

1,873
17
27

4

should be \d table_name. \dt table_name lists the relations. – l85m Sep 09 '15 at 20:23

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

40

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

1,904
2
24
36

answered Nov 17 '14 at 16:25

ka_lin

9,066
6
34
52

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 33 · Answer 6 · edited Apr 24 '18 at 18:56

33

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,245
5
25
30

answered Oct 16 '09 at 21:19

Russ Cam

121,250
30
197
258

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 17 · Answer 7 · edited Apr 11 '21 at 07:34

17

Call below code in MS SQL Server:

sp_columns [tablename]

edited Apr 11 '21 at 07:34

Amin Golmahalle

2,911
2
20
31

answered Oct 16 '09 at 21:14

Bryan

8,670
7
38
60

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

12

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

The Red Pea

14,933
15
89
118

answered May 03 '13 at 22:23

Leslie Sage

376
2
8

1

Did not work in SSMS 2012. Btw did you mean SQL Server Management Studio 2008? – Hammad Khan 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

6

For SQL Server

sp_help tablename

edited May 05 '21 at 04:52

answered Aug 12 '13 at 03:13

Prahalad Gaggar

10,932
16
49
68

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

4

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,797
18
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

Aaron Bertrand · Answer 11 · 2009-10-16T23:08:41.277

3

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('dbo.tablename');

edited Oct 16 '09 at 23:08

answered Oct 16 '09 at 22:59

Aaron Bertrand

261,961
36
448
471

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,790
1
16
24

answered Feb 27 '15 at 13:37

Matthew lowe

31
1

How do I list all the columns in a table?

12 Answers12

Linked

Related