SHOW COLUMNS from multiple tables

Question

I am trying to get the column names from 2 tables.

I tried a query like: (SHOW COLUMNS FROM users) UNION (SHOW COLUMNS FROM posts) but that does not work & returns a syntax error. I tried the same query using DESCRIBE but that did not work either. How can I get all the column names from multiple tables in a single query? Is it possible?

If it is of any interest to anyone, I wrote a *Describe All Tables* in [this Answer](http://stackoverflow.com/a/38679580) — Drew, Jul 31 '16 at 00:15

score 11 · Accepted Answer · edited May 20 '14 at 17:38

11

From the docs for version 5.0 (http://dev.mysql.com/doc/refman/5.0/en/show-columns.html)

"SHOW COLUMNS displays information about the columns in a given table"

So you can't really use it on multiple tables. However if you have information_schema database then you could use it like follows:

select column_name 
from `information_schema`.`columns` 
where `table_schema` = 'mydb' and `table_name` in ('users', 'posts');

Here you'd have to replace the mydb with your database name, or just use DATABASE().

edited May 20 '14 at 17:38

Flimm

136,138
45
251
267

answered Jul 24 '13 at 23:24

vee

38,255
7
74
78

@Namphibian, Whoops! :) – vee Jul 24 '13 at 23:26
4

No worries. All about the spirit of things. – Namphibian Jul 24 '13 at 23:27

score 5 · Answer 2 · answered Jul 24 '13 at 23:25

5

Yes use the information_Schema views.

SELECT * FROM information_schema.columns
WHERE Table_Name=? OR Table_name=?;

Use them as they are a standards way of querying database metadata.

answered Jul 24 '13 at 23:25

Namphibian

12,046
7
46
76

score 1 · Answer 3 · answered Oct 07 '16 at 07:34

1

If you also would like to get the name of the table column is from select table_name too

SELECT column_name, table_name 
FROM `information_schema`.`columns` 
WHERE `table_schema` = DATABASE() AND `table_name` in ('table1', 'table2');

answered Oct 07 '16 at 07:34

Buksy

11,571
9
62
69

Salman A · Answer 4 · 2021-11-05T10:34:52.643

I am assuming that you actually want to list all columns of the tables involved in a join.

There is a neat trick to view the qualified table and column names in a select statement. First EXPLAIN the select query, then look at the result of SHOW WARNINGS:

EXPLAIN SELECT * FROM users JOIN posts ON users.id = posts.user_id;
SHOW WARNINGS;

The result will look something like this:

Level	Code	Message
Note	1003	/* select#1 */ select `testdb`.`users`.`id` AS `id`,`testdb`.`users`.`name` AS `name`,`testdb`.`posts`.`id` AS `id`,`testdb`.`posts`.`user_id` AS `user_id`,`testdb`.`posts`.`name` AS `name` from `testdb`.`users` join `testdb`.`posts` where (`testdb`.`users`.`id` = `testdb`.`posts`.`user_id`)

The resulting query contains fully qualified name of all columns inside the select clause instead of *.

SHOW COLUMNS from multiple tables

4 Answers4

Linked