with simple_keys as (select
n.nspname as table_schema,
c.relname as table_name,
(SELECT a.attname FROM pg_attribute a WHERE a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE) AS column_name
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace n on c.relnamespace=n.oid
where o.contype in ('u', 'p')
and c.relkind = 'r'
and cardinality(o.conkey)=1
and n.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),
integer_columns AS (SELECT table_schema, table_name, column_name
FROM INFORMATION_SCHEMA.columns
WHERE data_type IN ('smallint', 'integer', 'bigint')
AND (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables
WHERE table_type IN ('BASE TABLE'))
AND 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))
SELECT table_schema, table_name, Count(*) AS number_of_simple_int_keys, string_agg (column_name, ';<br>' ORDER BY column_name) AS simple_int_keys
FROM simple_keys
WHERE (table_schema, table_name, column_name) IN (SELECT table_schema, table_name, column_name
FROM integer_columns)
GROUP BY table_schema, table_name
HAVING Count(*) >1
ORDER BY Count(*) DESC, table_schema, table_name;