Seeing Table Structure
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.
Re: Seeing Table Structure
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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeing Table Structure
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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeing Table Structure
Yes, please, email it to me. Tony -Original Message- From: Olaf Stein [EMAIL PROTECTED] To: [EMAIL PROTECTED]; MySql mysql@lists.mysql.com Sent: Mon, 23 Jul 2007 12:22 pm Subject: Re: Seeing Table Structure I don't know of any way of doing this for all tables. wrote a python script that creates a html file with information about ables (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. -- ySQL General Mailing List or list archives: http://lists.mysql.com/mysql o unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] AOL now offers free email to everyone. Find out more about what's free from AOL at AOL.com.
Re: Seeing Table Structure
That works. Thanks. Tony -Original Message- From: Mogens Melander [EMAIL PROTECTED] To: Olaf Stein [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; MySql mysql@lists.mysql.com Sent: Mon, 23 Jul 2007 12:29 pm Subject: Re: Seeing Table Structure 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 opy 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. - ater Mogens Melander 45 40 85 71 38 66 870 133 224 -- his message has been scanned for viruses and angerous content by MailScanner, and is elieved to be clean. - ySQL General Mailing List or list archives: http://lists.mysql.com/mysql o unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] AOL now offers free email to everyone. Find out more about what's free from AOL at AOL.com.
Re: Seeing Table Structure
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,
