Detailed explanation of mysql table type query example

SELECT 
    table_schema AS database_name,
    table_name
FROM 
    information_schema.tables
WHERE 
    table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND table_type = 'BASE TABLE'
    AND table_name NOT IN (
        SELECT DISTINCT table_name 
        FROM information_schema.partitions 
        WHERE partition_name IS NOT NULL
    )
ORDER BY 
    table_schema, table_name;

SELECT 
    p.table_schema AS database_name,
    p.table_name,
    GROUP_CONCAT(p.partition_name ORDER BY p.partition_ordinal_position) AS partitions,
    p.partition_method,
    p.partition_expression
FROM 
    information_schema.partitions p
WHERE 
    p.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND p.partition_name IS NOT NULL
GROUP BY 
    p.table_schema, p.table_name, p.partition_method, p.partition_expression
ORDER BY 
    p.table_schema, p.table_name;

SELECT 
    t.table_schema AS database_name,
    t.table_name,
    CASE 
        WHEN p.table_name IS NULL THEN 'Normal Table'
        ELSE 'Partition Table'
    END AS table_type,
    p.partition_method,
    p.partition_expression
FROM 
    information_schema.tables t
LEFT JOIN (
    SELECT DISTINCT 
        table_schema, 
        table_name,
        partition_method,
        partition_expression
    FROM 
        information_schema.partitions 
    WHERE 
        partition_name IS NOT NULL
) p ON t.table_schema = p.table_schema AND t.table_name = p.table_name
WHERE 
    t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND t.table_type = 'BASE TABLE'
ORDER BY 
    t.table_schema, t.table_name;

SELECT 
    t.table_schema AS 'Database Name',
    t.table_name AS 'Table name',
    CASE 
        WHEN p.table_name IS NULL THEN 'Normal Table'
        ELSE CONCAT('Partition table(', p.partition_method, ')')
    END AS 'Table Type',
    t.table_rows AS 'Number of data rows(Estimate)',
    CONCAT(ROUND(t.data_length / (1024 * 1024), 2), ' MB') AS 'Data size',
    CONCAT(ROUND(t.index_length / (1024 * 1024), 2), ' MB') AS 'Index Size',
    CONCAT(ROUND((t.data_length + t.index_length) / (1024 * 1024), 2), ' MB') AS 'Total Size',
    p.partition_expression AS 'Partition Key'
FROM 
    information_schema.tables t
LEFT JOIN (
    SELECT DISTINCT 
        table_schema, 
        table_name,
        partition_method,
        partition_expression
    FROM 
        information_schema.partitions 
    WHERE 
        partition_name IS NOT NULL
) p ON t.table_schema = p.table_schema AND t.table_name = p.table_name
WHERE 
    t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND t.table_type = 'BASE TABLE'
ORDER BY 
    t.table_schema, 
    CASE WHEN p.table_name IS NULL THEN 0 ELSE 1 END,  -- Normal table in front
    t.table_name;
SELECT 
    t.table_schema AS 'database',
    t.table_name AS 'Table name',
    CASE 
        WHEN p.partition_method IS NULL THEN 'Normal Table'
        ELSE CONCAT('Partition table(', p.partition_method, ')')
    END AS 'Table Type',
    t.table_rows AS 'Estimate行数',
    CONCAT(ROUND(t.data_length/1024/1024, 2), ' MB') AS 'Data size',
    p.partition_expression AS 'Partition Key'
FROM 
    information_schema.tables t
LEFT JOIN (
    SELECT 
        table_schema, 
        table_name,
        partition_method,
        partition_expression
    FROM 
        information_schema.partitions
    WHERE 
        partition_name IS NOT NULL
    GROUP BY 
        table_schema, table_name, partition_method, partition_expression
) p ON t.table_schema = p.table_schema AND t.table_name = p.table_name
WHERE 
    t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND t.table_type = 'BASE TABLE'
ORDER BY 
    t.table_schema, t.table_name;

SELECT 
    t.table_schema AS 'database',
    t.table_name AS 'Table name',
    CASE 
        WHEN p.partition_method IS NULL THEN 'Normal Table'
        ELSE CONCAT('Partition table(', p.partition_method, ')')
    END AS 'Table Type',
    t.table_rows AS 'Estimate the number of rows',
    p.partition_expression AS 'Partition key'
FROM 
    information_schema.tables t
LEFT JOIN (
    SELECT DISTINCT 
        table_schema, 
        table_name,
        partition_method,
        partition_expression
    FROM 
        information_schema.partitions
    WHERE 
        partition_name IS NOT NULL
) p ON t.table_schema = p.table_schema AND t.table_name = p.table_name
WHERE 
    t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND t.table_type = 'BASE TABLE'
ORDER BY 
    t.table_schema, t.table_name;