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.
 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;


Popular posts from this blog

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Database startup / shutdown /restart

How to Enable/Disable autovacuum on PostgreSQL

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

PostgreSQL Pgbadger Installation On Linux