WITH pk_columns AS (SELECT  table_schema,  table_name, c.column_name, c.is_nullable
FROM information_schema.table_constraints AS tc INNER JOIN information_schema.constraint_column_usage AS cu USING (table_schema, table_name, constraint_name)
INNER JOIN INFORMATION_SCHEMA.columns AS c USING (table_schema, table_name, column_name)
WHERE tc.constraint_type IN ('PRIMARY KEY')),
non_pk_columns AS (SELECT A.table_schema, A.table_name, A.column_name, A.is_nullable
FROM INFORMATION_SCHEMA.columns A
INNER JOIN information_schema.tables T
ON A.table_schema = T.table_schema
AND A.table_name = T.table_name
WHERE T.table_type='BASE TABLE' AND A.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) 
EXCEPT SELECT table_schema, table_name, column_name, is_nullable
FROM pk_columns), 
all_optional AS (SELECT table_schema, table_name
FROM non_pk_columns
GROUP BY table_schema, table_name
HAVING Count(*) FILTER (WHERE is_nullable='YES')  = Count(*)),
base_tables AS (SELECT A.table_schema, A.table_name 
FROM INFORMATION_SCHEMA.tables A
INNER JOIN INFORMATION_SCHEMA.schemata B
ON A.table_schema=B.schema_name
WHERE A.table_type='BASE TABLE'
AND (A.table_schema = 'public'
OR B.schema_owner<>'postgres'))
SELECT Count(*) AS number_of_tables_where_all_non_pk_columns_optional, 
(SELECT Count(*) AS number_of_base_tables 
FROM base_tables) AS number_of_base_tables,
Round((Count(*)::decimal*100/(SELECT Count(*) AS number_of_base_tables FROM base_tables)::decimal),1) AS percentage_of_base_tables
FROM all_optional;