Re: PG query to count all objects

Hi Prashant,

You may try running the following SQL query.

select
n.nspname as schema_name
,c.relname as object_name
,r.rolname as object_owner
,case c.relkind
when 'r' then 'TABLE'
when 'm' then 'MATERIALIZED_VIEW'
when 'i' then 'INDEX'
when 'S' then 'SEQUENCE'
when 'v' then 'VIEW'
when 'c' then 'TYPE'
else c.relkind::text
end as object_type
from pg_class c
join pg_roles r
on r.oid = c.relowner
join pg_namespace n
on n.oid = c.relnamespace
where n.nspname not in ('information_schema', 'pg_catalog')
and n.nspname not like 'pg_toast%'
order by n.nspname, c.relname;

--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com

On Thu, Apr 2, 2020 at 2:19 PM Prashant Kulkarni <ppk10(dot)prashant(at)gmail(dot)com>
wrote:

> Hi Team,
>
> Please can you help me with a query in PostgreSQL which will list the
> count of all objects in the database with respect (group by) object types
> ( Table/
> Index/Mview/Partition/Subpartition/Sequece/Lob/Procedure/Functions/..../etc)
>
> Thanks.
>