Query goal: | Find schemas without schema objects. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | High (Few or no false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Drop the unused schemas or add schema elements to these. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH schemas_with_objects AS ( SELECT n.nspname AS schema_name FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid INNER JOIN pg_class AS c ON n.oid=c.relnamespace WHERE (n.nspname='public' OR a.rolname<>'postgres') AND relkind NOT IN ('t') UNION SELECT n.nspname AS schema_name FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid INNER JOIN pg_type AS pt ON n.oid=pt.typnamespace WHERE (n.nspname='public' OR a.rolname<>'postgres') AND pt.typtype NOT IN ('b','c') UNION SELECT n.nspname AS schema_name FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid INNER JOIN pg_proc AS pc ON n.oid=pc.pronamespace WHERE (nspname='public' OR rolname<>'postgres')), all_user_schemas AS (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name='public' OR schema_owner<>'postgres') SELECT schema_name FROM all_user_schemas EXCEPT SELECT schema_name FROM schemas_with_objects ORDER BY schema_name; |
SQL query | Description |
---|---|
WITH schemas_with_objects AS ( SELECT n.nspname AS schema_name FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid INNER JOIN pg_class AS c ON n.oid=c.relnamespace WHERE (n.nspname='public' OR a.rolname<>'postgres') AND relkind NOT IN ('t') UNION SELECT n.nspname AS schema_name FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid INNER JOIN pg_type AS pt ON n.oid=pt.typnamespace WHERE (n.nspname='public' OR a.rolname<>'postgres') AND pt.typtype NOT IN ('b','c') UNION SELECT n.nspname AS schema_name FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid INNER JOIN pg_proc AS pc ON n.oid=pc.pronamespace WHERE (nspname='public' OR rolname<>'postgres')), all_user_schemas AS (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name='public' OR schema_owner<>'postgres') SELECT format('DROP SCHEMA %1$I;', schema_name) AS statements FROM all_user_schemas WHERE NOT EXISTS (SELECT * FROM schemas_with_objects WHERE all_user_schemas.schema_name=schemas_with_objects.schema_name) ORDER BY statements; | Drop the schemas. |
Collection name | Collection description |
---|---|
Find problems automatically | Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not . |
Category name | Category description |
---|---|
Schemas | Queries of this category provide information about databse schemas |
Unused implementation elements | Queries of this catergory provide information about the database objects that are not used. |