直接切换到对应的库下执行
代码语言：javascript复制select
nsp.nspname as SchemaName
,cls.relname as ObjectName
,rol.rolname as ObjectOwner
,case cls.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 cls.relkind::text
end as ObjectType
from pg_class cls
join pg_roles rol
on rol.oid = cls.relowner
join pg_namespace nsp
on nsp.oid = cls.relnamespace
where nsp.nspname not in ('information_schema', 'pg_catalog')
and nsp.nspname not like 'pg_toast%'
and rol.rolname = 'rw'
order by nsp.nspname, cls.relname;注意，这个不能获取到schema和database的归属情况，只能获取到表和序列的objectowner的情况
代码语言：javascript复制上面的这个sql，能查出下面的这种rolname=objectowner归属的
schemaname | objectname | objectowner | objecttype
------------ ------------ ------------- ------------
public | sq1l | rw | SEQUENCE
public | t1 | rw | TABLE
对于下面的这2种情况，都查不出来的。
1、表的owner是一个role，表里面access privilege又有其它的授权
2、非public的schema下
db2=# create schema myschema;
db1=# alter schema myschema owner to rw;
db1=# dn
List of schemas
Name | Owner
---------- ----------
myschema | rw
public | postgres
(2 rows)
db2=# d
List of relations
Schema | Name | Type | Owner
-------- ------ ------- ----------
public | tt | table | postgres
(1 row)
db2=# z tt
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
-------- ------ ------- --------------------------- ------------------- ----------
public | tt | table | postgres=arwdDxt/postgres | |
| | | dts=arwdDxt/postgres | |
(1 row)
