Copy SQL

WITH derived_tables AS (SELECT t.table_schema, t.table_name, vtu.view_schema, vtu.view_name, 'VIEW' AS type
FROM 
  information_schema.tables AS t LEFT JOIN information_schema.view_table_usage AS vtu
ON
  t.table_schema = vtu.table_schema AND
  t.table_name = vtu.table_name
WHERE t.table_type = 'BASE TABLE' AND t.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 DISTINCT 
source_ns.nspname as source_schema,
source_table.relname as source_table,
dependent_ns.nspname as dependent_schema,
dependent_view.relname as dependent_view,
'MATERIALIZED VIEW' AS type
FROM pg_depend 
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid 
JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid 
JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid 
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace
WHERE dependent_view.relkind='m'
AND dependent_ns.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 
(dependent_ns.nspname ||'.'|| dependent_view.relname<>source_ns.nspname||'.'||source_table.relname)) 
SELECT table_schema, 
table_name, 
string_agg('(' || type || ') ' || view_schema || '.'|| view_name,',
 ') AS list_of_derived_tables, 
Count(view_name) AS number_of_derived_tables,
Count(view_name) FILTER (WHERE type='VIEW') AS number_of_views,
Count(view_name) FILTER (WHERE type='MATERIALIZED VIEW') AS number_of_mat_views
FROM derived_tables
GROUP BY table_schema, table_name
ORDER BY Count(view_name) DESC, table_schema, table_name;