注:以下为postgresql,其他数据库的略过哈
\d[+] table_name 查看【详细】表结构
要获取某个schame下的所有表,或者获取某个表的所有字段,需要依赖这两个表:
information_schema.tables 与 information_schema.columns
第一个表存放的是所有schema下所有表的信息
\d information_schema.tables
View "information_schema.tables"
Column | Type | Modifiers
------------------------------+-----------------------------------+-----------
table_catalog | information_schema.sql_identifier |
table_schema | information_schema.sql_identifier |
table_name | information_schema.sql_identifier |
table_type | information_schema.character_data |
self_referencing_column_name | information_schema.sql_identifier |
reference_generation | information_schema.character_data |
user_defined_type_catalog | information_schema.sql_identifier |
user_defined_type_schema | information_schema.sql_identifier |
user_defined_type_name | information_schema.sql_identifier |
is_insertable_into | information_schema.yes_or_no |
is_typed | information_schema.yes_or_no |
commit_action | information_schema.character_data |
第二个表是存放所有schema下所有表的字段名称
\d information_schema.columns
View "information_schema.columns"
Column | Type | Modifiers
--------------------------+------------------------------------+-----------
table_catalog | information_schema.sql_identifier |
table_schema | information_schema.sql_identifier |
table_name | information_schema.sql_identifier |
column_name | information_schema.sql_identifier |
ordinal_position | information_schema.cardinal_number |
column_default | information_schema.character_data |
is_nullable | information_schema.yes_or_no |
data_type | information_schema.character_data |
character_maximum_length | information_schema.cardinal_number |
character_octet_length | information_schema.cardinal_number |
numeric_precision | information_schema.cardinal_number |
numeric_precision_radix | information_schema.cardinal_number |
numeric_scale | information_schema.cardinal_number |
datetime_precision | information_schema.cardinal_number |
interval_type | information_schema.character_data |
interval_precision | information_schema.cardinal_number |
character_set_catalog | information_schema.sql_identifier |
character_set_schema | information_schema.sql_identifier |
character_set_name | information_schema.sql_identifier |
collation_catalog | information_schema.sql_identifier |
collation_schema | information_schema.sql_identifier |
collation_name | information_schema.sql_identifier |
domain_catalog | information_schema.sql_identifier |
domain_schema | information_schema.sql_identifier |
domain_name | information_schema.sql_identifier |
udt_catalog | information_schema.sql_identifier |
udt_schema | information_schema.sql_identifier |
udt_name | information_schema.sql_identifier |
scope_catalog | information_schema.sql_identifier |
scope_schema | information_schema.sql_identifier |
scope_name | information_schema.sql_identifier |
maximum_cardinality | information_schema.cardinal_number |
dtd_identifier | information_schema.sql_identifier |
is_self_referencing | information_schema.yes_or_no |
is_identity | information_schema.yes_or_no |
identity_generation | information_schema.character_data |
identity_start | information_schema.character_data |
identity_increment | information_schema.character_data |
identity_maximum | information_schema.character_data |
identity_minimum | information_schema.character_data |
identity_cycle | information_schema.yes_or_no |
is_generated | information_schema.character_data |
generation_expression | information_schema.character_data |
is_updatable | information_schema.yes_or_no |
跟据这两张表的字段,要获取某个schema的所有表及获取某表的所有字段就会变得很容易。
获取某个schame下的所有表:
select table_name from information_schema.tables where table_schema='schema_name' and table_type='table_name';
获取某表所有字段
select column_name from information_schema.columns where table_schema='schema_name' and table_name='table_name'
获取某表存放文本的字段
select column_name,data_type from information_schema.columns where table_schema='table_schema' and table_name='table_name' and data_type in ('character varying', 'text');