UPDATE: see the next iteration on this project [here]
Here’s a view that will spit out just about everything MySQL (5.1) knows about the tables and fields it maintains for you. The first field can be joined to the output of something like
SELECT * FROM AZ_CA_NV_UT_species_LOCAL PROCEDURE ANALYSE(10000, 4000);
to see before and after “optimal” (1) (2) field types and lengths predicted by the internal PROCEDURE ANALYSE.
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_field_table_data` AS
SELECT
CONCAT_WS('.',`tables`.`TABLE_SCHEMA`,`columns`.`TABLE_NAME`,`columns`.`COLUMN_NAME`) AS `FIELD_NAME`,
`tables`.`TABLE_COMMENT` AS `TABLE_COMMENT`,
`columns`.`TABLE_NAME` AS `TABLE_NAME`,
`columns`.`COLUMN_NAME` AS `COLUMN_NAME`,
`columns`.`COLUMN_TYPE` AS `COLUMN_TYPE`,
`columns`.`DATA_TYPE` AS `DATA_TYPE`,
`columns`.`COLUMN_KEY` AS `COLUMN_KEY`,
`tables`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,
`tables`.`TABLE_TYPE` AS `TABLE_TYPE`,
`tables`.`ENGINE` AS `ENGINE`,
`tables`.`VERSION` AS `VERSION`,
`tables`.`ROW_FORMAT` AS `ROW_FORMAT`,
`tables`.`TABLE_ROWS` AS `TABLE_ROWS`,
`tables`.`AVG_ROW_LENGTH` AS `AVG_ROW_LENGTH`,
`tables`.`DATA_LENGTH` AS `DATA_LENGTH`,
`tables`.`MAX_DATA_LENGTH` AS `MAX_DATA_LENGTH`,
`tables`.`INDEX_LENGTH` AS `INDEX_LENGTH`,
`tables`.`AUTO_INCREMENT` AS `AUTO_INCREMENT`,
`tables`.`CREATE_TIME` AS `CREATE_TIME`,
`tables`.`UPDATE_TIME` AS `UPDATE_TIME`,
`tables`.`TABLE_COLLATION` AS `TABLE_COLLATION`,
`tables`.`CHECKSUM` AS `CHECKSUM`,
`tables`.`CREATE_OPTIONS` AS `CREATE_OPTIONS`,
`columns`.`ORDINAL_POSITION` AS `ORDINAL_POSITION`,
`columns`.`COLUMN_DEFAULT` AS `COLUMN_DEFAULT`,
`columns`.`IS_NULLABLE` AS `IS_NULLABLE`,
`columns`.`CHARACTER_MAXIMUM_LENGTH` AS `CHARACTER_MAXIMUM_LENGTH`,
`columns`.`CHARACTER_OCTET_LENGTH` AS `CHARACTER_OCTET_LENGTH`,
`columns`.`NUMERIC_PRECISION` AS `NUMERIC_PRECISION`,
`columns`.`NUMERIC_SCALE` AS `NUMERIC_SCALE`,
`columns`.`CHARACTER_SET_NAME` AS `CHARACTER_SET_NAME`,
`columns`.`COLLATION_NAME` AS `COLLATION_NAME`,
`columns`.`EXTRA` AS `EXTRA`,
`columns`.`PRIVILEGES` AS `PRIVILEGES`,
`columns`.`COLUMN_COMMENT` AS `COLUMN_COMMENT`,
NOW() AS `RUN_DATETIME`
FROM (`information_schema`.`tables`
JOIN `information_schema`.`columns`
ON (((`tables`.`TABLE_SCHEMA` = `columns`.`TABLE_SCHEMA`)
AND (`tables`.`TABLE_NAME` = `columns`.`TABLE_NAME`))))