Goal Find sequence generators that are not owned by a table column, i.e., if one drops the table or the column, then the sequence generator stays in place.
Notes The query checks as to whether a sequence depends on a column so that if the table/column is dropped, then the sequence will be dropped as well. The query also takes into account that if a column is an identity column, then internally an associated sequence generator is created that will be dropped if the table/column will be dropped.
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 Alter the sequence so that it will be owned by a column.
Data Source system catalog only
SQL Query 
WITH sequences AS (
SELECT 
c.oid,
n.nspname AS schema_name, 
c.relname AS sequence_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='S'
)
SELECT schema_name, sequence_name
FROM sequences seq
WHERE NOT EXISTS (SELECT 1
FROM pg_catalog.pg_depend d 
WHERE d.objid=seq.oid
AND d.deptype IN ('a','i')
AND d.refobjsubid<>0)
ORDER BY schema_name, sequence_name;

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:

Comfortability of database evolution

Queries of this category provide information about the means that influence database evolution.

Sequence generators

Queries of this category provide information about sequence generators and their usage.

NameDescription
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
Sequence generatorsQueries of this category provide information about sequence generators and their usage.