CODEWITHSUNDEEP
mysql
Sajad
Sajad

Reputation: 2363

Describe all tables in the database with a single statement?

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

Something like this:

describe * from myDB;

Upvotes: 65

Views: 106036

Answers (9)

Jaroslav
Jaroslav

Reputation: 917

Use

mysqldump -d -u -p -h

The -d option means "without data".

Already answered in: How to generate DDL for all tables in a database in MySQL

Upvotes: 0

sivi
sivi

Reputation: 11114

To specify each table explicitly:

DESCRIBE table_a; DESCRIBE table_b; DESCRIBE table_c;

Upvotes: 11

scottalan
scottalan

Reputation: 1694

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

Upvotes: 1

Englebert Lai Pak Fu
Englebert Lai Pak Fu

Reputation: 101

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

Upvotes: 10

Hamed Kamrava
Hamed Kamrava

Reputation: 12847

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';

Upvotes: 97

Kamal Soni
Kamal Soni

Reputation: 1552

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

Upvotes: 4

quantumferret
quantumferret

Reputation: 493

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;

Upvotes: 8

Fredrik Nyman
Fredrik Nyman

Reputation: 33

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

Upvotes: 2

Venu A Positive
Venu A Positive

Reputation: 3062

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

Upvotes: 0

Related Questions