如何查PostgreSQL 数据库中所有的表
[作者：技术者高健@博客园mail: luckyjackgao@gmail.com ]这个也是从 oid2name 中扒出来的：
$ ./oid2name -d postgres
From database "postgres":
now:
SELECT pg_catalog.pg_relation_filenode(c.oid) as "Filenode", relname as "Table Name"FROM pg_class cLEFT 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
FilenodeTable Name
----------------------
24608 gaotab
24604 testtab
$
执行结果就是这样，这里没有用我给出的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 = CASEWHEN 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: luckyjackgao@gmail.com ]
页:
[1]