List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:July 24 2007 5:00am
Subject:Re: Seeing Table Structure
View as plain text  
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 ----------------+
Thread
Seeing Table Structuretonylabarbara23 Jul
  • Re: Seeing Table StructureOlaf Stein23 Jul
    • Re: Seeing Table StructureMogens Melander23 Jul
      • Re: Seeing Table Structuretonylabarbara23 Jul
      • Re: Seeing Table StructureSteve Edberg24 Jul
    • Re: Seeing Table Structuretonylabarbara23 Jul
    • adress stored as an arrayRoss Hulford14 Sep
      • Re: adress stored as an arrayBaron Schwartz14 Sep