How to show the column names of a table?

Question

The case is simple: You have a MySQL database where you have only an SQL query interface and you want to know the database structure with queries. You can list tables with show tables; command, but how do you see the individual column names?

(SELECT statement shows Empty set if no data is present and can NOT be thus used.)

score 37 · Answer 1 · answered Sep 29 '11 at 09:22

37

select column_name from information_schema.columns where table_name='table'

answered Sep 29 '11 at 09:22

msi77

1,135
1
7
9

score 7 · Accepted Answer · edited Sep 27 '15 at 09:55

7

The best solution that I found out by myself was the desc table_name command. More information is on List MySQL Tables. This command gives the description of one database table, which is exactly what I was trying to find out.

edited Sep 27 '15 at 09:55

Peter Mortensen

350
2
10

answered Nov 09 '11 at 10:08

mico

511
3
7
19

score 6 · Answer 3 · edited Feb 19 '18 at 12:47

6

To make sure you list columns in a table in the current database, use the DATABASE() or SCHEMA() function. It returns NULL if you are not in a current database. This query will show the columns in a table in the order the columns were defined:

SELECT column_name,column_type
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND table_name='table'
ORDER BY ordinal_position;

edited Feb 19 '18 at 12:47

Michael Green

24,839
13
51
96

answered Sep 29 '11 at 15:28

RolandoMySQLDBA

182,700
33
317
520

score 3 · Answer 4 · answered Sep 29 '11 at 08:55

3

I'm not sure I understand what you mean with table titles, but you can get most of the information about tables and their columns from the INFORMATION_SCHEMA

answered Sep 29 '11 at 08:55

score 3 · Answer 5 · answered Sep 29 '11 at 09:48

how do you see the individual table titles?

Do you mean table comments?

use stack;
create table t(v integer primary key) comment 'My Special Table';

show tables;
+-----------------+
| Tables_in_stack |
+-----------------+
| t               |
+-----------------+

select table_name, table_comment from information_schema.tables where table_name='t';
+------------+------------------+
| table_name | table_comment    |
+------------+------------------+
| t          | My Special Table |
+------------+------------------+

Rolya Chuma · Answer 6 · 2016-12-16T12:06:52.893

2

SHOW COLUMNS FROM mydb.mytable;

where mydb - is the database that contains needed table

mytable - is the needed table

It return info of columns (for examp. names of columns, type, e t c)

edited Dec 16 '16 at 12:06

answered Dec 09 '16 at 15:02

Rolya Chuma

21
2

score 0 · Answer 7 · edited Feb 19 '18 at 12:50

You need to join information_schema.tables and information_schema.columns together to get the list of tables and their columns' details.

information_schema.columns not only shows detail about tables but also views. There is no way to filter only table details from this system view.

Hence you need to join.

Example query:

select t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME, c.COLUMN_TYPE, c.DATA_TYPE
from information_schema.tables t , information_schema.columns c where 2=2 
and t.table_schema=c.table_schema
and t.table_name=c.table_name
and t.table_type ='BASE TABLE' 
order by t.table_schema, t.table_name, c.column_name 
;

How to show the column names of a table?

7 Answers7