Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Script to find the count of objects for each Database Schema

In this post, I am sharing a script to find the total count of objects for each database schemas of PostgreSQL.
If you Postgres DBA and want to do accounting on a number of total objects per schemas, you can use this script.
Using this script, you can find different types of objects count like table, view, index, sequence.
SELECT
 n.nspname as schema_name
 ,CASE c.relkind
    WHEN 'r' THEN 'table'
    WHEN 'v' THEN 'view'
    WHEN 'i' THEN 'index'
    WHEN 'S' THEN 'sequence'
    WHEN 's' THEN 'special'
 END as object_type
 ,count(1) as object_count
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','i','S','s')
GROUP BY  n.nspname,
 CASE c.relkind
    WHEN 'r' THEN 'table'
    WHEN 'v' THEN 'view'
    WHEN 'i' THEN 'index'
    WHEN 'S' THEN 'sequence'
    WHEN 's' THEN 'special'
 END
ORDER BY n.nspname,
 CASE c.relkind
    WHEN 'r' THEN 'table'
    WHEN 'v' THEN 'view'
    WHEN 'i' THEN 'index'
    WHEN 'S' THEN 'sequence'
    WHEN 's' THEN 'special'
 END;

Comments

Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

PostgreSQL pgBadger

How to Return a Result Set from a PostgreSQL Stored Procedure

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

What are the Easy Tips To Increase AdSense CPC