If you have MySQL5 you can show table structure via the
'information_schema' pseudo-database. This has the advantage of
dumping column data for all tables in one output table. In addition,
you can select/sort the metadata using standard SQL:
[EMAIL PROTECTED]> show tables from information_schema;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| KEY_COLUMN_USAGE |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| VIEWS |
| USER_PRIVILEGES |
+---------------------------------------+
16 rows in set (0.00 sec)
[EMAIL PROTECTED]> describe information_schema.columns;
+--------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(1024) | YES | | NULL | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| COLUMN_NAME | varchar(64) | NO | | | |
| ORDINAL_POSITION | bigint(21) | NO | | 0 | |
| COLUMN_DEFAULT | varchar(64) | YES | | NULL | |
| IS_NULLABLE | varchar(3) | NO | | | |
| DATA_TYPE | varchar(64) | NO | | | |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) | YES | | NULL | |
| CHARACTER_OCTET_LENGTH | bigint(21) | YES | | NULL | |
| NUMERIC_PRECISION | bigint(21) | YES | | NULL | |
| NUMERIC_SCALE | bigint(21) | YES | | NULL | |
| CHARACTER_SET_NAME | varchar(64) | YES | | NULL | |
| COLLATION_NAME | varchar(64) | YES | | NULL | |
| COLUMN_TYPE | longtext | NO | | | |
| COLUMN_KEY | varchar(3) | NO | | | |
| EXTRA | varchar(20) | NO | | | |
| PRIVILEGES | varchar(80) | NO | | | |
| COLUMN_COMMENT | varchar(255) | NO | | | |
+--------------------------+---------------+------+-----+---------+-------+
19 rows in set (0.02 sec)
[EMAIL PROTECTED]> select table_name,column_name,data_type
from information_schema.columns where table_schema='sedberg' and
table_name like 'elp%' order by column_name;
+------------+-------------+-----------+
| table_name | column_name | data_type |
+------------+-------------+-----------+
| elpfiles | check_time | datetime |
| elpfiles | filename | varchar |
| elpfiles | file_base | varchar |
| elpfiles | file_date | datetime |
| elpfiles | file_ext | varchar |
| elpfiles | file_group | int |
| elpfiles | file_owner | int |
| elpfiles | file_sha1 | varchar |
| elpfiles | file_size | bigint |
| elpfiles | pathname | varchar |
| elpfiles | _id | int |
+------------+-------------+-----------+
11 rows in set (0.03 sec)
Keep in mind that (1) this database is read-only, and (2) I wouldn't
try joining other tables against information_schema tables for any
heavily-used applications. The information_schema information is
generated on-the-fly (as far as I understand it), so it has no
indices and queries cannot be optimized well.
See
http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
for more information.
steve
At 6:29 PM +0200 7/23/07, Mogens Melander wrote:
Try this:
# mysqldump -d DBNAME > tables.sql
--no-data, -d
Do not write any row information for the table.
This is very useful if you want to dump only
the CREATE TABLE statement for the table.
On Mon, July 23, 2007 18:22, Olaf Stein wrote:
I don't know of any way of doing this for all tables.
I wrote a python script that creates a html file with information about
tables (engine, fields,keys,indices)
If you are interested in it I can email it
Olaf
On 7/23/07 11:34 AM, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
Hi;
I need to see the table structure of all the tables of a database I have. I
could also just dump all the data from all the tables in the
database and copy
the database, thus grabbing the table structures. I can´t find in the
documentation how to do either of those. Please advise.
TIA,
Tony
________________________________________________________________________
AOL now offers free email to everyone. Find out more about
what's free from
>> AOL at AOL.com.
--
+--------------- my people are the people of the dessert, ---------------+
| Steve Edberg http://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center [EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+---------------- said t e lawrence, picking up his fork ----------------+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
