Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

find Orphaned Sequence, not owned by any Column

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.
SELECT 
 ns.nspname AS SchemaName
 ,c.relname AS SequenceName
FROM pg_class AS c
JOIN pg_namespace AS ns 
 ON c.relnamespace=ns.oid
WHERE c.relkind = 'S'
  AND NOT EXISTS (SELECT * FROM pg_depend WHERE objid=c.oid AND deptype='a')
ORDER BY c.relname;

No comments:

Post a Comment