CODEWITHSUNDEEP
mysqldatabasedatabase-designddl
sumit
sumit

Reputation: 11257

How to generate DDL for all tables in a database in MySQL

How to generate the DDL for all tables in a database of MySQL at once. I know that the following query will output the DDL for a table. But I want DDL of all tables at once because I am having hundreds of tables in my database.

show create table <database name>.<table name>;

For example:

show create table projectdb.customer_details;

The above query will result in DDL of customer_details table.

I am using MySQL with MySQL workbench on Windows OS.

Upvotes: 57

Views: 85775

Answers (3)

AJ.
AJ.

Reputation: 28184

You can do it using the mysqldump command line utility:

mysqldump -d -u <username> -p -h <hostname> <dbname>

The -d option means "without data".

Upvotes: 102

John Toups
John Toups

Reputation: 1

@tftdias above made a comment that is partially correct:

The issue was the space between the -p and 'pps' as the password. You can absolutely freetext your password on the command line. You just shouldn't, as a general security rule. In linux, with a proper configuration, you can add a space (" ") BEFORE the first character on your command line, and that line will not enter into 'history'. I would recommend that whenever you cleartext your password (don't do it!), that you at least put a space first so that the password is not in visible history.

Upvotes: 0

Ovais Khatri
Ovais Khatri

Reputation: 3211

You must get list of all the tables in database and then run this query. check this link to get list of all tables: http://php.net/manual/en/function.mysql-list-tables.php

Upvotes: -1

Related Questions