MySQL Data Report

mysqlDataReport.sql

-- Report on table data within a database using information_schema to inspect the data
-- copied to https://gist.github.com/kehh/2ef188881002b10e4b3c
USE mecc;
SET @sql = NULL;
-- may need to be bigger depending on how many tables, columns you have and the length of data in those columns
SET @@group_concat_max_len = 500000000;
SELECT GROUP_CONCAT(
    CONCAT_WS('',
              'SELECT "', TABLE_NAME, '" AS tableName, "',
              COLUMN_NAME , '" AS field,
              COUNT(DISTINCT ', COLUMN_NAME ,') AS distinctValues,
              COUNT(`', COLUMN_NAME ,'`) AS numRecords,
              MIN(LENGTH(`', COLUMN_NAME ,'`)) AS minLength,
              MAX(LENGTH(`', COLUMN_NAME ,'`)) AS maxLength,
              AVG(LENGTH(`', COLUMN_NAME ,'`)) AS meanLength,
              GROUP_CONCAT(DISTINCT TRIM(`', COLUMN_NAME , '`) SEPARATOR "|") AS sampleValues
              FROM `', TABLE_NAME, '`
              WHERE `', COLUMN_NAME, '` IS NOT NULL
              AND trim(`', COLUMN_NAME, '`) != ""
              GROUP BY 1 ' ) SEPARATOR "UNION \n\t") INTO @sql
FROM information_schema.COLUMNS where TABLE_SCHEMA = 'mecc';
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;