如何查PostgreSQL 数据库中所有的表
发布网友
发布时间：2022-04-22 12:09
共1个回答
热心网友
时间：2023-10-13 07:29
[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
