--Below is for finding objects under schema scott: Replace your schema_name with scott
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
where n.nspname ='
scott
'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
NOTE
: Make sure that, the schema_name for which you are looking for objects, is present in the search_path of that user. Otherwise it wont return any rows
postgres=# show search_path;
search_path
-----------------------
"$user", public,
scott
(1 row)