How to get all columns' names for all the tables in MySQL?

Question

Is there a fast way of getting all column names from all tables in MySQL, without having to list all the tables?

I only need a quick overview over the database. Its not gonna be code in an app. — dieter, Apr 13 '11 at 11:36

score 401 · Accepted Answer · edited Jul 04 '22 at 18:54

401

select column_name from information_schema.columns
where table_schema = 'your_db'
order by table_name,ordinal_position

edited Jul 04 '22 at 18:54

João Pimentel Ferreira

14,289
10
80
109

answered Apr 13 '11 at 11:50

Nicola Cossu

54,599
15
92
98

that was what i was looking for :-) Wrote a java app doing the same stuff by now – dieter Apr 13 '11 at 11:57
1

Thanks! To get just the column names, use this: select column_name from information_schema.columns where table_schema = 'your_db' order by table_name, ordinal_position – Phil Goetz Jun 01 '15 at 21:49
How about to get the count of filled data in front of the column names? I have the same problem plus data count – Gulmuhammad Akbari Jun 05 '18 at 05:55
how about knowing the primary key? – David Walser Jul 19 '18 at 11:29

score 59 · Answer 2 · edited Nov 16 '13 at 16:25

59

To list all the fields from a table in MySQL:

select * 
  from information_schema.columns 
 where table_schema = 'your_DB_name' 
   and table_name = 'Your_tablename'

edited Nov 16 '13 at 16:25

Ben

51,770
36
127
149

answered Nov 22 '12 at 05:49

suganya

591
4
2

1

Interesting - I get "Unknown column 'column_name' in 'where clause' – brianlmerritt Dec 03 '20 at 11:00

score 41 · Answer 3 · edited Aug 08 '16 at 13:40

41

it is better that you use the following query to get all column names easily

Show columns from tablename

edited Aug 08 '16 at 13:40

Community

1
1

answered Jul 16 '16 at 12:55

rajesh satpute

411
4
4

1

Sometimes you need a solution that doesn't rely on information_schema – Bradley M. Davis Jul 22 '19 at 04:04
Also this gives only the columns of the given table `tablename` which is not what OP asked for. – jmizv Jul 07 '23 at 09:47

score 31 · Answer 4 · answered Jun 10 '15 at 13:56

31

SELECT * FROM information_schema.columns
WHERE table_schema = DATABASE()
ORDER BY table_name, ordinal_position

Since I don't have enough rep to comment, here's a minor improvement (in my view) over nick rulez's excellent answer: replacing WHERE table_schema = 'your_db' with WHERE table_schema = DATABASE().

answered Jun 10 '15 at 13:56

appel

423
4
10

That's helpful. It always annoys me to hardcode things like that when it can be avoided. Thanks. – David Aug 27 '15 at 22:01

score 25 · Answer 5 · edited Oct 13 '16 at 16:05

25

On the offchance that it's useful to anyone else, this will give you a comma-delimited list of the columns in each table:

SELECT table_name,GROUP_CONCAT(column_name ORDER BY ordinal_position)
FROM information_schema.columns
WHERE table_schema = DATABASE()
GROUP BY table_name
ORDER BY table_name

Note : When using tables with a high number of columns and/or with long field names, be aware of the group_concat_max_len limit, which can cause the data to get truncated.

edited Oct 13 '16 at 16:05

IceNV

5
1
3

answered Dec 22 '15 at 17:11

trapper_hag

780
9
14

2

I like this one... since I could easily copy all the column names. – jatazoulja Feb 01 '18 at 01:18

score 6 · Answer 6 · edited Jan 29 '13 at 11:10

6

<?php
        $table = 'orders';
        $query = "SHOW COLUMNS FROM $table";
        if($output = mysql_query($query)):
            $columns = array();
            while($result = mysql_fetch_assoc($output)):
                $columns[] = $result['Field'];
            endwhile;
        endif;
        echo '<pre>';
        print_r($columns);
        echo '</pre>';
?>

edited Jan 29 '13 at 11:10

Madhu

2,429
16
31

answered Jan 29 '13 at 10:48

Ali Nawaz

1,006
10
11

1

Anyone reading this in 2016 - mysql_query has become deprecated. Best to use PHP's mysqli going forward. – JCutting8 Jul 31 '16 at 02:50

score 5 · Answer 7 · edited May 23 '17 at 11:54

5

Similar to the answer posted by @suganya this doesn't directly answer the question but is a quicker alternative for a single table:

DESCRIBE column_name;

edited May 23 '17 at 11:54

Community

1
1

answered Jan 26 '17 at 19:10

Philip Kirkbride

21,381
38
125
225

This will print a table and not just the table's column names. – k0pernikus Jun 28 '17 at 15:35

score 5 · Answer 8 · answered Jan 26 '18 at 13:33

The question was :

Is there a fast way of getting all COLUMN NAMES from all tables in MySQL, without having to list all the tables?

SQL to get all information for each column

select * from information_schema.columns
where table_schema = 'your_db'
order by table_name,ordinal_position

SQL to get all COLUMN NAMES

select COLUMN_NAME from information_schema.columns
where table_schema = 'your_db'
order by table_name,ordinal_position

score 3 · Answer 9 · answered Nov 30 '15 at 20:32

I wrote this silly thing a long time ago and still actually use it now and then:

https://gist.github.com/kphretiq/e2f924416a326895233d

Basically, it does a "SHOW TABLES", then a "DESCRIBE " on each table, then spits it out as markdown.

Just edit below the "if name" and go. You'll need to have pymysql installed.

score 2 · Answer 10 · answered Oct 14 '16 at 02:14

2

Piggybacking on Nicola's answer with some readable php

$a = mysqli_query($conn,"select * from information_schema.columns
where table_schema = 'your_db'
order by table_name,ordinal_position");
$b = mysqli_fetch_all($a,MYSQLI_ASSOC);
$d = array();
foreach($b as $c){
    if(!is_array($d[$c['TABLE_NAME']])){
        $d[$c['TABLE_NAME']] = array();
    }
    $d[$c['TABLE_NAME']][] = $c['COLUMN_NAME'];
}
echo "<pre>",print_r($d),"</pre>";

answered Oct 14 '16 at 02:14

Scot Nery

671
7
19

4

'some readable php'..Naming variables with a,b ,c is not what I would call readable – Kaymaz May 03 '18 at 14:10

Alon Barad · Answer 11 · 2021-06-24T10:52:14.650

2

You can query all columns per table in specific database using this query

select
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME
from
    information_schema.columns
where
    table_schema = 'database_name'
order by 
    table_name,
    ordinal_position;

edited Jun 24 '21 at 10:52

answered Jun 24 '21 at 08:04

Alon Barad

1,491
1
13
26

score 0 · Answer 12 · answered Dec 28 '22 at 19:48

You can simply get all columns of a table using information_schema.columns just add group_concat to get a comma separated list of columns.

select group_concat( column_name ) as ColumnNames from information_schema.columns where table_schema = 'ur_db_name' and table_name = 'ur_tbl_name'

How to get all columns' names for all the tables in MySQL?

12 Answers12

Linked