如何查PostgreSQL 数据库中所有的表

[作者：技术者高健@博客园  mail: [e-mail] ]

这个也是从 oid2name 中扒出来的：

[postgres@localhost bin]$ ./oid2name -d postgres
From database "postgres":
now: 
 SELECT pg_catalog.pg_relation_filenode(c.oid) as "Filenode", relname as "Table Name"  FROM pg_class c  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace   LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database(),pg_catalog.pg_tablespace t WHERE relkind IN ('r') AND   n.nspname NOT IN ('pg_catalog', 'information_schema') AND n.nspname !~ '^pg_toast' AND t.oid = CASE                     WHEN reltablespace <> 0 THEN reltablespace     ELSE dattablespace               END ORDER BY relname 
  Filenode  Table Name
----------------------
     24608      gaotab
     24604     testtab
[postgres@localhost bin]$ 

 执行结果就是这样，这里没有用我给出的  postgres 数据库名 ，而是用了 pg_catalog.current_database()

我把格式整理一下，并且把数据库名换成我想要的 ’postgres‘，当然，如果有其他数据库，换其他的名字就可以了。

 SELECT         
    pg_catalog.pg_relation_filenode(c.oid) as "Filenode",
    relname as "Table Name"  
    
FROM     
    pg_class c  
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace  
    LEFT JOIN pg_catalog.pg_database d ON d.datname = 'postgres',     
    pg_catalog.pg_tablespace t 
WHERE     
    relkind IN ('r') 
    AND n.nspname NOT IN ('pg_catalog', 'information_schema')
    AND n.nspname !~ '^pg_toast'
    AND t.oid = CASE  WHEN reltablespace <> 0 THEN reltablespace   ELSE dattablespace   END 
    
ORDER BY    
     relname 

其实 t 是没有必要的，还可以再简化：

SELECT         
    pg_catalog.pg_relation_filenode(c.oid) as "Filenode",
    relname as "Table Name"  
    
FROM     
    pg_class c  
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace  
    LEFT JOIN pg_catalog.pg_database d ON d.datname = 'postgres'   
     
WHERE     
    relkind IN ('r') 
    AND n.nspname NOT IN ('pg_catalog', 'information_schema')
    AND n.nspname !~ '^pg_toast'
    
ORDER BY    
     relname

 

 [作者：技术者高健@博客园  mail: [e-mail] ]