Columns with JSON, JSONB, or XML type

Copy SQL

WITH cols AS (SELECT table_schema, table_name, table_type, column_name, data_type
FROM INFORMATION_SCHEMA.columns INNER JOIN INFORMATION_SCHEMA.tables USING (table_schema, table_name)
WHERE table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT n.nspname AS table_schema, c.relname AS table_name, 'MATERIALIZED VIEW' AS table_type, at.attname AS column_name, 
CASE WHEN t.typtype='d' THEN bt.typname ELSE t.typname END AS data_type
FROM pg_class AS c INNER JOIN pg_namespace AS n ON c.relnamespace=n.oid
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_attribute AS at ON c.oid=at.attrelid 
INNER JOIN pg_type AS t ON at.atttypid=t.oid
LEFT JOIN pg_type AS bt ON t.typbasetype=bt.oid
WHERE (n.nspname='public' OR rolname<>'postgres')
AND c.relkind='m'
AND at.attisdropped = false
AND at.attnum>=1)
SELECT table_schema, table_name, table_type, column_name, data_type
FROM cols
WHERE data_type~*'(xml|json)'
ORDER BY table_type, table_schema, table_name;