tencent cloud

SELECT
    t1.TABLE_SCHEMA,
    t1.TABLE_NAME,
    t1.COLUMN_NAME,
    t1.DATA_TYPE
FROM
    INFORMATION_SCHEMA.COLUMNS t1
INNER JOIN (
    -- Subquery: First identify tables that meet the criteria (number of columns of specific types >= 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') -- Please 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 query filters the type again to ensure only columns causing risks 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: Combine and display column names in a single row.
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;