Postgresql 导出表结构信息

项目用了Postgresql 数据库,项目组要出表结构的文档,手写太麻烦,想用slq脚本导出一份。查了一番资料,似乎没有多好的方法。dump方式导出的脚本太乱,没法直接写在word文档里。只能自己写sql查询出表结构,然后利用pgadmin的导出查询结果的功能,能最快的获取一份整个数据库的表结构信息。虽然不能实现全自动的导出文档,但是对整理文档来说,还是节省了不少时间。

--查询所有的表字段信息(带表名)

select

(select relname||'--'||(select description from pg_description where objoid=oid and objsubid=0) as comment from pg_class where oid=a.attrelid) as table_name,

a.attname as column_name,

format_type(a.atttypid,a.atttypmod) as data_type,

(case when atttypmod-4>0 then atttypmod-4 else 0 end)data_length,

(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y' else 'N' end) as 主键约束,

(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y' else 'N' end) as 唯一约束,

(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y' else 'N' end) as 外键约束,

(case when a.attnotnull=true then 'Y' else 'N' end) as nullable,

col_description(a.attrelid,a.attnum) as comment

from pg_attribute a

where attstattarget=-1 and attrelid in (select oid from pg_class where relname in(select relname from pg_class where relkind ='r' and relname like 'exg_%'))

order by table_name,a.attnum;

执行sql语句,然后将查询结果导出

使用英文逗号做分隔符,导出csv文件,可以直接在excle中编辑

使用本地字符集可以防止乱码

 用excle打开导出的csv文件,因为导出的是所有的表结构信息,所以要先分表,拷贝第一行表头信息,分别插入到每张表的前面(插入行的快捷键自己网上找吧)。

做完上面的步骤,就可以把表结构信息粘帖到word文档里了;

在粘贴完毕后,要选择使用目标格式

表格出来了

这个时候所有的表结构是一张大表格,可以用Ctrl+shift+enter把表格分开

附:其它sql脚本

--查询表名和描述

select relname as table_name,(select description from pg_description where objoid=oid and objsubid=0) as comment from pg_class where relkind ='r' and relname like 'exg_%' order by table_name;

 

--查询表字段信息

select

a.attname as column_name,

format_type(a.atttypid,a.atttypmod) as data_type,

(case when atttypmod-4>0 then atttypmod-4 else 0 end)data_length,

(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y' else 'N' end) as 主键约束,

(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y' else 'N' end) as 唯一约束,

(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y' else 'N' end) as 外键约束,

(case when a.attnotnull=true then 'Y' else 'N' end) as nullable,

col_description(a.attrelid,a.attnum) as comment

from pg_attribute a

where attstattarget=-1 and attrelid = (select oid from pg_class where relname ='exg_ms_alarm');--表名

posted on 2014-11-21 11:02  nami  阅读(17752)  评论(0编辑  收藏  举报