iT邦幫忙

1

PostgreSQL 查詢使用者擁有的物件(Table, Sequence, Index等)

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 'table' 
        WHEN 'I' THEN '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 c.relkind IN ('r','p','v','m','S','f','')
   AND n.nspname <> 'pg_catalog'
   AND n.nspname <> 'information_schema'
   AND n.nspname !~ '^pg_toast'
   AND pg_catalog.pg_table_is_visible(c.oid)
 ORDER BY 1,3,2;

+--------+----------------------+----------+-------+
| Schema |         Name         |   Type   | Owner |
+--------+----------------------+----------+-------+
| miku1  | dept_deptid_seq      | sequence | miku  |
| miku1  | emp_empid_seq        | sequence | miku  |
...
| miku1  | xx190720             | table    | miku  |
| miku1  | xx190721             | table    | miku  |
+--------+----------------------+----------+-------+


尚未有邦友留言

立即登入留言