tencent cloud

SELECT
    t1.TABLE_SCHEMA,
    t1.TABLE_NAME,
    t1.COLUMN_NAME,
    t1.DATA_TYPE
FROM
    INFORMATION_SCHEMA.COLUMNS t1
INNER JOIN (
    -- Subquery: First, find the tables that meet the condition (number of specific-type columns >= 2).
    SELECT
        TABLE_SCHEMA,
        TABLE_NAME
    FROM
        INFORMATION_SCHEMA.COLUMNS
    WHERE
        DATA_TYPE IN ('blob', 'mediumblob', 'longblob', 'json', 'text', 'MEDIUMTEXT', 'LONGTEXT', 'geometry')
        AND TABLE_SCHEMA IN ('xxx', 'xxxx') -- Replace with actual database names.
    GROUP BY
        TABLE_SCHEMA,
        TABLE_NAME
    HAVING
        COUNT(*) >= 2
) t2 ON t1.TABLE_SCHEMA = t2.TABLE_SCHEMA AND t1.TABLE_NAME = t2.TABLE_NAME
WHERE
    -- Outer layer filters types again to ensure only risky columns are listed.
    t1.DATA_TYPE IN ('blob', 'mediumblob', 'longblob', 'json', 'text', 'MEDIUMTEXT', 'LONGTEXT', 'geometry');

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    COUNT(*) as risky_column_count,
    GROUP_CONCAT(COLUMN_NAME) as risky_columns -- Optional: Concatenate column names into a single row for display.
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    DATA_TYPE IN ('blob', 'mediumblob', 'longblob', 'json', 'text', 'MEDIUMTEXT', 'LONGTEXT', 'geometry')
    AND TABLE_SCHEMA IN ('xxx', 'xxxx')
GROUP BY
    TABLE_SCHEMA,
    TABLE_NAME
HAVING
    COUNT(*) >= 2;