deeejay

psql - List all objects and owners in a database

Aug 12th, 2015
503
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select nsp.nspname as object_schema,
  2.        cls.relname as object_name,
  3.        rol.rolname as owner,
  4.        case cls.relkind
  5.          when 'r' then 'TABLE'
  6.          when 'i' then 'INDEX'
  7.          when 'S' then 'SEQUENCE'
  8.          when 'v' then 'VIEW'
  9.          when 'c' then 'TYPE'
  10.          else cls.relkind::text
  11.        end as object_type
  12. from pg_class cls
  13.   join pg_roles rol on rol.oid = cls.relowner
  14.   join pg_namespace nsp on nsp.oid = cls.relnamespace
  15. where nsp.nspname not in ('information_schema', 'pg_catalog')
  16.   and nsp.nspname not like 'pg_toast%'
  17.   --and rol.rolname = current_user  --- uncomment this if you want to see all objects for the current user
  18. order by nsp.nspname, cls.relname;
Advertisement
Add Comment
Please, Sign In to add comment