WITH dependencies AS (SELECT DISTINCT nt.nspname AS table_schema, d.refobjid::regclass::text AS table_name, 
CASE WHEN c.relkind='r' THEN 'BASE TABLE'
WHEN c.relkind='f' THEN 'FOREIGN TABLE'
WHEN c.relkind='v' THEN 'VIEW'
ELSE 'MATERIALIZED VIEW' END AS table_type,
nv.nspname AS dependent_table_schema, v.oid::regclass::text AS dependent_table_name,
CASE WHEN v.relkind='v' THEN 'VIEW'
ELSE 'MATERIALIZED VIEW' END AS dependent_table_type, 
v.relnatts AS number_of_dependent_table_columns,
regexp_replace(pg_get_viewdef(v.oid),'[\r\n]','
','g') AS dependent_view_def
FROM pg_attribute AS a 
   JOIN pg_depend AS d
      ON d.refobjsubid = a.attnum AND d.refobjid = a.attrelid
   JOIN pg_rewrite AS r 
      ON r.oid = d.objid
   JOIN pg_class AS v 
      ON v.oid = r.ev_class
      JOIN pg_namespace AS nv
	ON nv.oid=v.relnamespace
   JOIN pg_class AS c
	ON c.oid=d.refobjid
   JOIN pg_namespace AS nt
	ON nt.oid=c.relnamespace
WHERE v.relkind IN  ('v','m')
  AND d.classid = 'pg_rewrite'::regclass
  AND d.refclassid = 'pg_class'::regclass 
  AND d.deptype = 'n'
  AND a.attisdropped='f'
    AND nt.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) 
AND nv.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))
SELECT dependent_table_schema, 
dependent_table_name, 
dependent_table_type,  
number_of_dependent_table_columns,
dependent_view_def,
Count(*) AS number_of_underlying_tables,
string_agg(table_schema || '.' || table_name || '(' || table_type || ')', ';
' ORDER BY table_schema, table_name) AS underlying_tables
FROM dependencies
WHERE number_of_dependent_table_columns>15
GROUP BY dependent_table_schema, dependent_table_name, 
dependent_table_type,  
number_of_dependent_table_columns,
dependent_view_def
HAVING Count(*)>5
ORDER BY Count(*) DESC, number_of_dependent_table_columns DESC, dependent_table_schema, dependent_table_name;