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;
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;
On Thu, Apr 2, 2020 at 2:19 PM Prashant Kulkarni <ppk10.prashant@xxxxxxxxx> 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.
