This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script to find orphaned Sequence objects which not owned by any column of PostgreSQL Database.
When we define a column using SERIAL or BIGSERIAL, PostgreSQL internally creates one sequence for it and associate with it.
In RDBMS, A Sequence is an independent object, and you can use same sequence object for multiple tables as well.
In a development environment, we are executing DDL frequently commands like multiple CREATE or DROP statements, so there is possibility of Orphaned Sequence.
A Database Administrator can use this script to remove unwanted Sequence and make sure that Postgres database is up to date.
ns.nspname AS SchemaName
,c.relname AS SequenceName
FROM pg_class AS c
JOIN pg_namespace AS ns
WHERE c.relkind = 'S'
AND NOT EXISTS (SELECT * FROM pg_depend WHERE objid=c.oid AND deptype='a')
ORDER BY c.relname;