Query to get all user defined objects in PostgreSQL
To get all the user defined objects in a database in PostgreSQL use the below query. This will list tables , views , indexes, constraints etc
SELECT ‘processsuitedb’as “DBNAME”,n.nspname as “SCHEMA_NAME”, c.relname AS “OBJECT_NAME”,relkind as “OBJECT_TYPE”,
CASE c.relkind
WHEN ‘r’ THEN ‘USER_TABLE’
WHEN ‘v’ THEN ‘VIEW’
WHEN ‘m’ THEN ‘VIEW’
WHEN ‘i’ THEN ‘INDEX’
WHEN ‘S’ THEN ‘SEQUENCE’
END as “DESCRIPTION”
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname=’public’ AND n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’, ‘information_schema’)
union
select ‘processsuitedb’as “DBNAME”,n.nspname as “SCHEMA_NAME”,c.conname as “OBJECT_NAME”, ‘c’ as “OBJECT_TYPE”,’CONSTRAINT’ as “DESCRIPTION”
from pg_catalog.pg_constraint c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.connamespace
WHERE n.nspname=’public’ AND n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’, ‘information_schema’)
union
select ‘processsuitedb’as “DBNAME”,n.nspname as “SCHEMA_NAME”,c.proname as “OBJECT_NAME”, ‘f’ as “OBJECT_TYPE”,’FUNCTION’ as “DESCRIPTION”
from pg_catalog.pg_proc c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.pronamespace
WHERE n.nspname=’public’ AND n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’, ‘information_schema’)
union
SELECT ‘processsuitedb’as “DBNAME”,n.nspname as “SCHEMA_NAME”,t.tgname as “OBJECT_NAME”, ‘t’ as “OBJECT_TYPE”,’TRIGGER’ as “DESCRIPTION”
FROM pg_catalog.pg_trigger t
join pg_catalog.pg_class c on t.tgrelid = c.oid
JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
WHERE n.nspname=’public’ AND n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’, ‘information_schema’);
SELECT ‘processsuitedb’as “DBNAME”,n.nspname as “SCHEMA_NAME”, c.relname AS “OBJECT_NAME”,relkind as “OBJECT_TYPE”,
CASE c.relkind
WHEN ‘r’ THEN ‘USER_TABLE’
WHEN ‘v’ THEN ‘VIEW’
WHEN ‘m’ THEN ‘VIEW’
WHEN ‘i’ THEN ‘INDEX’
WHEN ‘S’ THEN ‘SEQUENCE’
END as “DESCRIPTION”
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname=’public’ AND n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’, ‘information_schema’)
union
select ‘processsuitedb’as “DBNAME”,n.nspname as “SCHEMA_NAME”,c.conname as “OBJECT_NAME”, ‘c’ as “OBJECT_TYPE”,’CONSTRAINT’ as “DESCRIPTION”
from pg_catalog.pg_constraint c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.connamespace
WHERE n.nspname=’public’ AND n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’, ‘information_schema’)
union
select ‘processsuitedb’as “DBNAME”,n.nspname as “SCHEMA_NAME”,c.proname as “OBJECT_NAME”, ‘f’ as “OBJECT_TYPE”,’FUNCTION’ as “DESCRIPTION”
from pg_catalog.pg_proc c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.pronamespace
WHERE n.nspname=’public’ AND n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’, ‘information_schema’)
union
SELECT ‘processsuitedb’as “DBNAME”,n.nspname as “SCHEMA_NAME”,t.tgname as “OBJECT_NAME”, ‘t’ as “OBJECT_TYPE”,’TRIGGER’ as “DESCRIPTION”
FROM pg_catalog.pg_trigger t
join pg_catalog.pg_class c on t.tgrelid = c.oid
JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
WHERE n.nspname=’public’ AND n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’, ‘information_schema’);
Viswamitra's blog 