查询表名、注释、数据行数、字段数(APP)

环境

系统平台：Linux x86-64 Red Hat Enterprise Linux 7
版本：5.6.5

文档用途

满足同时查出表名、注释情况、数据行数及字段数的需求。

详细信息

查询表名、注释、数据行数、字段数

文中需要用到user_tab_comment和user_col_comments视图，可参考实现达梦user_tab_comments和user_col_comments视图(APP)

现有三张表如下：

highgo=# \dt

              关联列表

 架构模式 | 名称  |  类型  | 拥有者

----------+-------+--------+--------

 public   | test  | 数据表 | highgo

 public   | test1 | 数据表 | highgo

 public   | test2 | 数据表 | highgo

(3 行记录)

注释情况及表中数据如下：

highgo=# select count(*) from test;

 count

-------

    10

(1 行记录)

highgo=# select count(*) from test1;

 count

-------

   100

(1 行记录)

highgo=# select count(*) from test2;

 count

-------

  1000

(1 行记录)

highgo=# select * from user_tab_comments;

 table_name | table_type | comments | owner

------------+------------+----------+--------

 test       | table      | hahahaha | public

 test1      | table      |          | public

 test2      | table      |          | public

(3 行记录)

highgo=# select * from user_col_comments;

 owner  | table_name | column_name | description

--------+------------+-------------+-------------

 public | test       | id          | hhhhh

 public | test       | info        |

 public | test1      | id          |

 public | test1      | info        |

 public | test2      | id          |

 public | test2      | info        |

(6 行记录)

首先创建所需要的函数：

create or replace function public.count_rows(varchar, varchar) returns bigint as $$

declare

    row_count bigint;

    tmpsql varchar;

begin

    tmpsql := 'select count(*) from '||$1||'.'||$2||';';

    execute tmpsql into row_count;

return row_count;

end;

$$ language plpgsql;

接下来即可查询，可按实际要求修改SQL：

highgo=# select

highgo-#     it.table_name as table_name,

highgo-#     utc.comments as comments,

highgo-#     cc.column_counts as column_counts,

highgo-#     public.count_rows(it.table_schema, it.table_name) as row_counts

highgo-# from

highgo-#     information_schema.tables it

highgo-# left join public.user_tab_comments utc on it.table_name = utc.table_name

highgo-# left join (

highgo(#     select

highgo(#         count(*) as column_counts,

highgo(#         table_name

highgo(#     from

highgo(#         public.user_col_comments

highgo(#     group by

highgo(#         table_name

highgo(# ) cc on cc.table_name = utc.table_name

highgo-# where

highgo-#     it.table_schema in (current_user, 'public');

        table_name        | comments | column_counts | row_counts

--------------------------+----------+---------------+------------

 pg_stat_statements       |          |               |         81

 user_tab_comments        |          |               |          3

 pg_wait_sampling_current |          |               |          7

 pg_wait_sampling_history |          |               |       5000

 user_col_comments        |          |               |          6

 pg_wait_sampling_profile |          |               |         30

 test                     | hahahaha |             2 |         10

 test1                    |          |             2 |        100

 test2                    |          |             2 |       1000

(9 行记录)