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:
mysql-5.0.15-log@localhost> 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)
mysql-5.0.15-log@localhost> 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)
mysql-5.0.15-log@localhost> 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, "tonylabarbara@stripped" <tonylabarbara@stripped>
> 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 sbedberg@stripped |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+---------------- said t e lawrence, picking up his fork ----------------+
