All derived tables that use joining tables

Copy SQL

WITH view_defs AS (SELECT 
views.table_schema, 
views.table_name, 
'VIEW' AS type,
views.view_definition
FROM 
information_schema.views
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 schemaname, matviewname, 'MATERIALIZED VIEW' AS type, definition
FROM pg_catalog.pg_matviews)
SELECT table_schema, table_name, type, regexp_replace(regexp_replace(view_definition,'[\r\n]','<br>','g'),'USING','<b>USING</b>','gi') AS definition
FROM view_defs
WHERE view_definition~*'(JOIN|WHERE.*[_[:alnum:]]+\.[_[:alnum:]]+[[:space:]]*=[[:space:]]*[_[:alnum:]]+\.[_[:alnum:]]+)'
ORDER BY table_schema, table_name, type;