/*
    MySQL database data dictionary

    Returns:
      - Schema, table and field names
      - Data type, length, precision and scale
      - Column position and nullability
      - Estimated record count
      - Data, index and total allocated space
*/

SELECT
    c.TABLE_SCHEMA                              AS `schema`,
    c.TABLE_NAME                                AS `tableName`,
    c.COLUMN_NAME                               AS `fieldName`,
    c.DATA_TYPE                                 AS `type`,
    c.CHARACTER_MAXIMUM_LENGTH                  AS `length`,
    c.NUMERIC_PRECISION                         AS `precision`,
    c.NUMERIC_SCALE                             AS `scale`,
    c.ORDINAL_POSITION                          AS `column_id`,
    c.IS_NULLABLE                               AS `nullable`,
    c.COLUMN_DEFAULT                            AS `defaultValue`,

    t.TABLE_ROWS                                AS `RowCount`,
    ROUND(t.DATA_LENGTH / 1024)                  AS `DataSpaceKB`,
    ROUND(t.INDEX_LENGTH / 1024)                 AS `IndexSpaceKB`,
    ROUND((t.DATA_LENGTH + t.INDEX_LENGTH)
          / 1024)                               AS `TotalSpaceKB`,
    ROUND(t.DATA_FREE / 1024)                    AS `FreeSpaceKB`,

    t.ENGINE                                    AS `engine`,
    t.AVG_ROW_LENGTH                            AS `AverageRowLength`,
    t.CREATE_TIME                               AS `CreatedDate`,
    t.UPDATE_TIME                               AS `LastUpdated`

FROM information_schema.COLUMNS c

JOIN information_schema.TABLES t
  ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
 AND t.TABLE_NAME   = c.TABLE_NAME
 AND t.TABLE_TYPE   = 'BASE TABLE'

WHERE c.TABLE_SCHEMA NOT IN
(
    'information_schema',
    'mysql',
    'performance_schema',
    'sys'
)

-- Optional: restrict the result to selected schemas
-- AND c.TABLE_SCHEMA IN ('application_database')

ORDER BY
    c.TABLE_SCHEMA,
    c.TABLE_NAME,
    c.ORDINAL_POSITION;