The list of all the queries

Coverage by derived tables

Query goal: Find for each base table the list of derived tables (both views and materialized views) that refer to the base table. If the database is used through the public database interface (virtual data layer), then, ideally, each table is referred from the subquery of at least one derived table.
Notes about the query: In case of the string_agg function, the line break (br) tag is used as a part of the separator for the better readability in case the query result is displayed in a web browser.
Query type: General (Overview of some aspect of the database.)
Query license: MIT License
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

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,',<br> ') 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;

Collections where the query belongs to

Collection nameCollection description
Find problems by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .

Categories where the query belongs to

Category nameCategory description
Derived tablesQueries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.

The list of all the queries