How can I describe all tables in the database through one statement?

Question

Is there any statement that can describe all tables in a database?

Something like this:

describe * from myDB;

Maybe, you can combine SHOW TABLES; and DESCRIBE for each table. — user4035, Nov 16 '13 at 16:23
possible duplicate of [Mysql get all columns from all tables](http://stackoverflow.com/questions/5648420/mysql-get-all-columns-from-all-tables) — Ben, Nov 16 '13 at 16:23
There's no single statement that can do it; both [show columns](http://dev.mysql.com/doc/refman/4.1/en/show-columns.html) and [describe](http://dev.mysql.com/doc/refman/4.1/en/describe.html) require a table name, so you can either do it every time or query the information schema. — Ben, Nov 16 '13 at 16:24
Related, if it's of interest, I wrote a *Describe All Tables* in [this Answer](http://stackoverflow.com/a/38679580). — Drew, Jul 31 '16 at 00:59
Possible duplicate of [MySQL schema describe to Github Wiki?](http://stackoverflow.com/questions/38665946/mysql-schema-describe-to-github-wiki) — Drew, Jul 31 '16 at 01:00
A close vote to point it to, though a brand new question, one that has an answer on it that does exactly what you want. Full Disclaimer: an answer I just wrote up. — Drew, Jul 31 '16 at 01:01

score 92 · Accepted Answer · edited Apr 03 '23 at 05:38

92

There is no statement that describe all tables at once. Here's some options:

SELECT * FROM information_schema.columns WHERE table_schema = 'db_name';
SELECT * FROM information_schema.columns WHERE table_schema = 'db_name' ORDER BY TABLE_NAME, ORDINAL_POSITION;
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, COLUMN_COMMENT, ORDINAL_POSITION FROM information_schema.columns WHERE table_schema = 'db_name' ORDER BY TABLE_NAME, ORDINAL_POSITION;
SELECT * FROM information_schema.columns WHERE table_schema != 'information_schema';

edited Apr 03 '23 at 05:38

Rob Bednark

25,981
23
80
125

answered Nov 16 '13 at 16:32

Hamed Kamrava

12,359
34
87
125

27

Good solution, but even better as `SELECT * FROM information_schema.columns WHERE table_schema = 'db_name' ORDER BY TABLE_NAME, ORDINAL_POSITION`. Or what I use to keep things short and sweet: `SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, COLUMN_COMMENT, ORDINAL_POSITION FROM information_schema.columns WHERE table_schema = 'db_name' ORDER BY TABLE_NAME, ORDINAL_POSITION`. – Alex Shaffer Jul 15 '16 at 11:45
3

i think what @AlexShaffer says is more approriate because or else the statement gives all the columns which is hard to even refer – Dilini Peiris Feb 08 '18 at 03:21
@AlexShaffer's comment should be it's own answer, I think. It's excellent and easy to work with. – quantumferret Apr 26 '18 at 21:54
I wanted to see a few more columns from my tables as compared to @alex-shaffer , so I used something more like this `SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, COLUMN_DEFAULT, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, COLUMN_KEY, EXTRA, ORDINAL_POSITION FROM information_schema.columns WHERE ( table_schema = 'db_name' AND TABLE_NAME like 'table_name') ORDER BY TABLE_NAME, ORDINAL_POSITION;` – Brad Knowles Jun 09 '20 at 21:53
2

if you're lazy like me just copy `SELECT * FROM information_schema.columns WHERE table_schema != 'information_schema'` and get all schemas at once – james.c.funk Sep 27 '21 at 15:17

score 12 · Answer 2 · edited Apr 03 '23 at 06:28

12

To specify each table explicitly:

DESCRIBE table_a; DESCRIBE table_b; DESCRIBE table_c;

edited Apr 03 '23 at 06:28

Rob Bednark

25,981
23
80
125

answered Aug 28 '14 at 15:47

sivi

10,654
2
52
51

2

This is obviously not ideal for situations where you might have hundreds of tables. Pretty much a non-answer. – Kees Briggs Jun 23 '17 at 18:18
5

"hack for a small db" is already describing the situation that calls for this solution. Your program should calculate for the prompt size and not ignore it (by assuming certain size of data always). Not all answers are panacea, and in fact, only few are. – sivi Jun 24 '17 at 06:14

score 10 · Answer 3 · edited Apr 03 '23 at 05:39

I am using linux way. First create a ~/.my.cnf to store the username and password for mysql. Next use the snippet below and run it in the linux terminal.

Generate the tables list and filter the header and awk to generate the column. Then, use the same method to DESC table_name.

for i in $(mysql MYDBNAME -e 'SHOW TABLES' \
| grep -v "Tables_in" \
| awk '{print $1}'); do \
    echo "TABLE: $i"; 
    mysql MYDBNAME -e "DESC $i"; \
done

score 8 · Answer 4 · answered Apr 27 '18 at 05:53

8

This is a variation of @AlexShaffer's excellent comment, modified to mirror what the Mac terminal's mysql monitor outputs when asked to describe a table.

USE information_schema;

SELECT TABLE_NAME 'Table', COLUMN_NAME 'Field', COLUMN_TYPE 'Type', IS_NULLABLE 'Null',
  COLUMN_KEY 'Key', COLUMN_DEFAULT 'Default', EXTRA 'Extra'
FROM information_schema.columns
WHERE table_schema = 'your_db'
ORDER BY TABLE_NAME;

answered Apr 27 '18 at 05:53

quantumferret

473
6
12

The use of ORDINAL_POSITION in the ORDER BY clause from that version could possibly improve your variation (which is closer in spirit to the output from DESCRIBE tablename). – Paul Campbell Apr 27 '18 at 12:51

Kamal Soni · Answer 5 · 2018-05-25T03:31:57.163

Please create the bash script like below and it will prompt you for details.

LINUX ONLY - BASH SCRIPT - describe-all-tables.sh

#!/bin/sh

echo ""
read -p 'MySQL db: ' DB
echo ""
read -p 'MySQL user: ' USER
echo ""
read -e -p 'MySQL host: ' -i "localhost" HOSTNAME
echo ""
read -s -p 'MySQL password: ' PASSWORD
echo ""

mysql -N -u${USER} ${DB} -p${PASSWORD} -h ${HOSTNAME} --execute="show tables" | while read table; do mysql -u${USER} -h ${HOSTNAME} ${DB} -p${PASSWORD} -v -t --execute="describe $table"; echo -e "\n"; done

USAGE - /bin/sh describe-all-tables.sh

score 2 · Answer 6 · edited Feb 24 '16 at 14:59

2

mysql -B -N -u root -pPASSWORD -h somehost \
-e "SELECT DISTINCT CONCAT('describe ', table_name, ';') AS query FROM information_schema.tables WHERE table_schema='DATABASE_NAME_HERE' " | \
mysql -B -N -u root -pPASSWORD -h somehost DATABASE_NAME_HERE

edited Feb 24 '16 at 14:59

domis86

1,227
11
9

answered Sep 14 '15 at 21:16

Fredrik Nyman

33
5

score 1 · Answer 7 · edited Apr 03 '23 at 05:40

1

Not sure if there is a way to get the results to display in a "table" format as it does when running the command from the mysql prompt, but this should describe all tables formatted vertically.

mysql -N -uUSER -pPASSWORD DATABASE_NAME --execute="show tables" \
| while read table; do \
    mysql -uUSER -pPASSWORD DATABASE_NAME --execute="describe $table \G"; \
done

edited Apr 03 '23 at 05:40

Rob Bednark

25,981
23
80
125

answered May 24 '18 at 22:02

scottalan

1,584
1
12
7

score -1 · Answer 8 · answered Dec 09 '14 at 11:51

By default, Mysql not describe all tables in the database. The main reason Database main intention Just decentralize power and take care of metadata, but not index the data.

Connect to the database: mysql [-u username] [-h hostname] database-name

To list all databases, in the MySQL prompt type: show databases

Then choose the right database: use MyDB;

List all tables in the database: show tables;

Describe a table: desc table-name or describe table-name

perfect answer for drilling down into the structure! – Shanimal Apr 12 '17 at 18:39 — Shanimal, Apr 12 '17 at 18:39

How can I describe all tables in the database through one statement?

8 Answers8

Linked