SQL 列出 postgresql information_schema 中的所有表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2276644/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
List all tables in postgresql information_schema
提问by littleK
What is the best way to list all of the tables within PostgreSQL's information_schema?
列出 PostgreSQL 的 information_schema 中所有表的最佳方法是什么？
To clarify: I am working with an empty DB (I have not added any of my own tables), but I want to see every table in the information_schema structure.
澄清一下：我正在使用一个空数据库（我没有添加任何自己的表），但我想查看 information_schema 结构中的每个表。
回答by RodeoClown
You should be able to just run select * from information_schema.tablesto get a listing of every table being managed by Postgres for a particular database.
您应该能够运行select * from information_schema.tables以获取 Postgres 为特定数据库管理的每个表的列表。
You can also add a where table_schema = 'information_schema'to see just the tables in the information schema.
您还可以添加where table_schema = 'information_schema'以仅查看信息架构中的表。
回答by phsaires
For listing your tables use:
要列出您的表，请使用：
SELECT table_name FROM information_schema.tables WHERE table_schema='public'
It will only list tables that you create.
它只会列出您创建的表。
回答by phsaires
\dt information_schema.
from within psql, should be fine.
从 psql 中，应该没问题。
回答by Chris Shoesmith
The "\z" COMMANDis also a good way to list tables when inside the interactive psql session.
在“\ Z” COMMAND也是一个不错的办法交互式会话PSQL内部时，清单表。
eg.
例如。
# psql -d mcdb -U admin -p 5555
mcdb=# /z
Access privileges for database "mcdb"
Schema | Name | Type | Access privileges
--------+--------------------------------+----------+---------------------------------------
public | activities | table |
public | activities_id_seq | sequence |
public | activities_users_mapping | table |
[..]
public | v_schedules_2 | view | {admin=arwdxt/admin,viewuser=r/admin}
public | v_systems | view |
public | vapp_backups | table |
public | vm_client | table |
public | vm_datastore | table |
public | vmentity_hle_map | table |
(148 rows)
回答by Timofey
You may use also
你也可以使用
select * from pg_tables where schemaname = 'information_schema'
In generall pg* tables allow you to see everything in the db, not constrained to your permissions (if you have access to the tables of course).
一般来说，pg* 表允许您查看数据库中的所有内容，而不受您的权限限制（当然，如果您有权访问这些表）。
回答by germanlinux
For private schema 'xxx'in postgresql :
对于'xxx'postgresql 中的私有模式：
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'xxx' AND table_type = 'BASE TABLE'
Without table_type = 'BASE TABLE', you will list tables and views
没有table_type = 'BASE TABLE'，您将列出表和视图
回答by hzh
1.get all tables and views from information_schema.tables, include those of information_schema and pg_catalog.
1.从information_schema.tables中获取所有表和视图，包括information_schema和pg_catalog。
select * from information_schema.tables
2.get tables and views belong certain schema
2.get 表和视图属于特定模式
select * from information_schema.tables
where table_schema not in ('information_schema', 'pg_catalog')
3.get tables only(almost \dt)
3.只获取表（几乎\dt）
select * from information_schema.tables
where table_schema not in ('information_schema', 'pg_catalog') and
table_type = 'BASE TABLE'
回答by Sally Levesque
If you want a quick and dirty one-liner query:
如果你想要一个快速而肮脏的单行查询：
select * from information_schema.tables
select * from information_schema.tables
You can run it directly in the Query tool without having to open psql.
您可以直接在查询工具中运行它，而无需打开 psql。
(Other posts suggest nice more specific information_schema queries but as a newby, I am finding this one-liner query helps me get to grips with the table)
（其他帖子提出了更好的更具体的信息模式查询，但作为新手，我发现这个单行查询可以帮助我掌握表格）
