How to dump a DESC of each and every table in MySQL (in csv format)

Question

I need to generate CSV files of DESC TableName for few hundred tables in a database.

Could you please help me with it guys? I am sure there is a way using Bash script. I tried using mysqldump but it is for data dump and sql dump of structure :(

Thank you!

Answer 1

On MySQL 5.0 on onwards, you can use the information_schema (http://dev.mysql.com/doc/refman/5.0/en/information-schema.html)

Example:

SELECT      CASE ordinal_position 
                WHEN 1 THEN CONCAT('Table ', table_schema, '.', table_name, '\n')
                ELSE ''
            END
,           column_name
,           column_type
,           is_nullable
,           column_key
,           column_default
,           extra
FROM        information_schema.columns
WHERE       table_schema = SCHEMA()
ORDER BY    table_schema
,           table_name
,           ordinal_position

The output here should be equivalent (but not identical) to what you get from DESC

Note that in this case, the query only reports the tables from the current database. Tweak the WHERE clause to fit your requirements.

Also a word of warning: these queries can be very slow, esp. if they run the first time.