Goal This query identifies empty schemas within the database. A schema is considered empty if it exists as a namespace but contains no database objects, such as tables, views, functions, or types. The presence of such schemas often indicates artifacts from failed or incomplete migrations, obsolete application components, or setup errors, and they can be safely removed to reduce schema clutter.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability High (Few or no false-positive results)
License MIT (opens in new tab)
Fixing Suggestion Drop the unused schemas or add schema elements to these.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
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 statements that help generate fixes for the identified problem.

Fix Action #1
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.

SQL Query to Generate FixDescription
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.

Collections

This query belongs to the following collections:

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 .

NameDescription
Find problems automaticallyQueries, 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 .

Categories

This query is classified under the following categories:

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.

NameDescription
SchemasQueries of this category provide information about databse schemas
Unused implementation elementsQueries of this catergory provide information about the database objects that are not used.