postgre 生成数据库html文档_postgresql 导出数据字典文档

--1.0--2015-11-30--postgresql-9.4.5--打印出数据字典html--执行完毕，在pgAdmin的消息窗口，把打印内容拷贝到文本文件中，替换掉多余的输出：[PGSCRIPT ] ，删除头部的[QUERY ]及打印出的查询语句，--最后把文件另存为.html文件。--用浏览器打开保存的网页，然后拷贝页面内容到word文档中，下面整理格式就可以了--注意：--脚本里包含了详细版，和简版两个版本的数据字典，使用的时候注意切换到对应的标题--'

列名类型长度主键约束唯一约束外键约束可否为空描述';--'列名类型描述';--2016-2-16 修正表字段注释(描述)为空，字段不打印的问题

begin

--查询表名

set @table = select distinct relname, relname||'('||(select description from pg_description where objoid = oid and objsubid = 0) ||'表'||')' astable_namefrompg_class c,pg_attribute awhere c.oid=a.attrelidand attstattarget=-1

and attrelid in(select oid from pg_class where relname in (select relname as table_name from pg_class where relkind='r' and relname like 'exg_%' order byrelname))order bytable_name;--数据字典(详细版)：列名 类型 长度 主键约束 唯一约束 外键约束 可否为空 描述

set @att = select (select relname from pg_class where oid=a.attrelid) astable_name,

a.attname,

format_type(a.atttypid,a.atttypmod),

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

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

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

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

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

col_description(a.attrelid,a.attnum)from pg_attribute a where attstattarget=-1 and attrelid in(select oid from pg_class where relname in (select relname as table_name from pg_class where relkind='r' and relname like 'exg_%' order byrelname))order bytable_name,attnum;/*--数据字典(简版)：列名 类型 描述

set @att = select (select relname from pg_class where oid=a.attrelid) as table_name,

,a.attname

,format_type(a.atttypid,a.atttypmod)

,col_description(a.attrelid,a.attnum)

from pg_attribute a

where attstattarget=-1

and attrelid in(select oid from pg_class where relname in (select relname as table_name from pg_class where relkind='r' and relname like 'exg_%' order by relname))

order by table_name,attnum;*/

--打印html文档

print '';print '';print '

';print '';print '数据字典';print '';set @i=0;while @i < lines(@table)begin

set @table_name = @table[@i][0];print @table[@i][1];print '

列名	类型	长度	主键约束	唯一约束	外键约束	可否为空	描述
列名	类型	描述

set @j=0;while @j < lines(@att)begin

if @att[@j][0] = @table_name

begin

--详细

print '

';print @att[@j][1];print '';print @att[@j][2];print '';print @att[@j][3];print '';print @att[@j][4];print '';print @att[@j][5];print '';print @att[@j][6];print '';print @att[@j][7];print '';print @att[@j][8];print '';--简版

/*print '

';

print @att[@j][1];

print '

';

print @att[@j][2];

print '

';

print @att[@j][3];

print '

';*/

end

set @j=@j+1;end

print '

';set @i=@i+1;end

end

--附：/*--数据字典--详细版

select

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

a.attname as 列名,

format_type(a.atttypid,a.atttypmod) as 类型,

(case when atttypmod-4>0 then atttypmod-4 else 0 end) as 长度,

(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 可否为空,

col_description(a.attrelid,a.attnum) as 描述

from pg_attribute a where attstattarget=-1 and attrelid in(select oid from pg_class where relname in (select relname as table_name from pg_class where relkind='r' and relname like 'exg_%' order by relname))

order by 表名,attnum;

--数据字典--简版

select

(select relname from pg_class where oid=a.attrelid) as table_name,

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

a.attname as 列名,

format_type(a.atttypid,a.atttypmod) as 类型,

col_description(a.attrelid,a.attnum) as 描述

from pg_attribute a where attstattarget=-1 and attrelid in(select oid from pg_class where relname in (select relname as table_name from pg_class where relkind='r' and relname like 'exg_%' order by relname))

order by table_name,attnum;*/