获取schema所有表&&获取某表所有字段

注：以下为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');