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?
Salman A
252k80 gold badges423 silver badges513 bronze badges
asked Jul 24, 2013 at 23:12
1
From the docs for version 5.0 [//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[]
.
Flimm
122k39 gold badges235 silver badges247 bronze badges
answered Jul 24, 2013 at 23:24
veevee
37.7k7 gold badges71 silver badges74 bronze badges
3
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, 2013 at 23:25
NamphibianNamphibian
11.8k7 gold badges45 silver badges73 bronze badges
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 7, 2016 at 7:34
BuksyBuksy
11k8 gold badges61 silver badges68 bronze badges
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:
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 *
.
13.7.7.5 SHOW COLUMNS Statement
SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
SHOW COLUMNS
displays information about the columns in a given table. It also works for views. SHOW COLUMNS
displays information only for
those columns for which you have some privilege.
mysql> SHOW COLUMNS FROM City;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int[11] | NO | PRI | NULL | auto_increment |
| Name | char[35] | NO | | | |
| CountryCode | char[3] | NO | MUL | | |
| District | char[20] | NO | | | |
| Population | int[11] | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
An alternative to
syntax is tbl_name
FROM db_name
db_name.tbl_name
. These two statements are equivalent:
SHOW COLUMNS FROM mytable FROM mydb;
SHOW COLUMNS FROM mydb.mytable;
The optional EXTENDED
keyword causes the output to include information about hidden columns that MySQL uses internally and are not accessible by users.
The optional FULL
keyword causes the output to include the column collation and comments, as well as the privileges you have for
each column.
The LIKE
clause, if present, indicates which column names to match. The WHERE
clause can be given to select rows using more general conditions, as discussed in Section 26.8, “Extensions to SHOW Statements”.
The
data types may differ from what you expect them to be based on a CREATE TABLE
statement because MySQL sometimes changes data types when you create or alter a table. The conditions under which this occurs are described in
Section 13.1.20.7, “Silent Column Specification Changes”.
SHOW COLUMNS
displays the following values for each table column:
Field
The name of the column.
Type
The column data type.
Collation
The collation for nonbinary string columns, or
NULL
for other columns. This value is displayed only if you use theFULL
keyword.Null
The column nullability. The value is
YES
ifNULL
values can be stored in the column,NO
if not.Key
Whether the column is indexed:
If
Key
is empty, the column either is not indexed or is indexed only as a secondary column in a multiple-column, nonunique index.If
Key
isPRI
, the column is aPRIMARY KEY
or is one of the columns in a multiple-columnPRIMARY KEY
.If
Key
isUNI
, the column is the first column of aUNIQUE
index. [AUNIQUE
index permits multipleNULL
values, but you can tell whether the column permitsNULL
by checking theNull
field.]If
Key
isMUL
, the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column.
If more than one of the
Key
values applies to a given column of a table,Key
displays the one with the highest priority, in the orderPRI
,UNI
,MUL
.A
UNIQUE
index may be displayed asPRI
if it cannot containNULL
values and there is noPRIMARY KEY
in the table. AUNIQUE
index may display asMUL
if several columns form a compositeUNIQUE
index; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value.Default
The default value for the column. This is
NULL
if the column has an explicit default ofNULL
, or if the column definition includes noDEFAULT
clause.Extra
Any additional information that is available about a given column. The value is nonempty in these cases:
auto_increment
for columns that have theAUTO_INCREMENT
attribute.on update CURRENT_TIMESTAMP
forTIMESTAMP
orDATETIME
columns that have theON UPDATE CURRENT_TIMESTAMP
attribute.VIRTUAL GENERATED
orSTORED GENERATED
for generated columns.DEFAULT_GENERATED
for columns that have an expression default value.
Privileges
The privileges you have for the column. This value is displayed only if you use the
FULL
keyword.Comment
Any comment included in the column definition. This value is displayed only if you use the
FULL
keyword.
Table column information is also available from the INFORMATION_SCHEMA
COLUMNS
table. See Section 26.3.8, “The INFORMATION_SCHEMA COLUMNS Table”. The extended information about hidden columns is available only using SHOW EXTENDED COLUMNS
; it cannot be
obtained from the COLUMNS
table.
You can list a table's columns with the mysqlshow db_name
tbl_name
command.
The
DESCRIBE
statement provides information similar to SHOW
COLUMNS
. See Section 13.8.1, “DESCRIBE Statement”.
The
SHOW CREATE TABLE
, SHOW TABLE STATUS
, and SHOW INDEX
statements also provide information about tables. See
Section 13.7.7, “SHOW Statements”.
In MySQL 8.0.30 and later, SHOW COLUMNS
includes the table's generated invisible primary key, if it has one, by default. You can cause this information to be suppressed in the statement's output by setting
show_gipk_in_create_table_and_information_schema
= OFF
. For more information, see Section 13.1.20.11, “Generated Invisible Primary Keys”.