当前位置:首页 > 联系方式 >

postgresql 导出数据字典文档

编辑:北京盛典时光文化传媒有限公司时间:2017-09-06 10:57:54阅读次数:2
postgresql 导出数据字典文档

项目上需要整理目前数据库的数据字典文档。项目不规范,这种文档只要后期来补。这么多张表,每个字段都写到word文档里真心头大。就算前面写了个查询表结构的sql,但是最后整理到word里还是感觉有点麻烦。以前写过一个oracle直接生成表结构的html文档,所以现在也想再弄个postgresql 版本的。查了一番文档,发现pg9.4不支持写文件。无奈放弃。最后选了一个这种方案,利用sql脚本中打印消息的功能。把生成的html文档打印出来,最后拷贝html文档代码到文本文件中保存,虽然比oracle那个麻烦了点,总算能得到想要的html文档了。

slq脚本:

--1.0 --2015-11-30 --postgresql-9.4.5 --打印出数据字典html --执行完毕,在pgAdmin的消息窗口,把打印内容拷贝到文本文件中,替换掉多余的输出:[PGSCRIPT ] ,删除头部的[QUERY ]及打印出的查询语句, --最后把文件另存为.html文件。 --用浏览器打开保存的网页,然后拷贝页面内容到word文档中,下面整理格式就可以了 --注意: --脚本里包含了详细版,和简版两个版本的数据字典,使用的时候注意切换到对应的标题 --'<tr><td>列名</td><td>类型</td><td>长度</td><td>主键约束</td><td>唯一约束</td><td>外键约束</td><td>可否为空</td><td>描述</td></tr>'; --'<tr><td>列名</td><td>类型</td><td>描述</td></tr>'; begin --查询表名 set @table = select distinct relname, relname||'('||(select description from pg_description where objoid = oid and objsubid = 0) ||'表'||')' as table_name from pg_class c,pg_attribute a where c.oid=a.attrelid and 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; --数据字典(详细版):列名 类型 长度 主键约束 唯一约束 外键约束 可否为空 描述 set @att = select (select relname from pg_class where oid=a.attrelid) as table_name, '<tr><td>'||a.attname||'</td>' ||'<td>'||format_type(a.atttypid,a.atttypmod)||'</td>' ||'<td>'||(case when atttypmod-4>0 then atttypmod-4 else 0 end)||'</td>' ||'<td>'||(case when (select count(*) from pg_constraint where conrelid=a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y' else 'N' end)||'</td>' ||'<td>'||(case when (select count(*) from pg_constraint where conrelid=a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y' else 'N' end)||'</td>' ||'<td>'||(case when (select count(*) from pg_constraint where conrelid=a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y' else 'N' end)||'</td>' ||'<td>'||(case when a.attnotnull=true then 'Y' else 'N' end)||'</td>' ||'<td>'||col_description(a.attrelid,a.attnum)||'</td></tr>' 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; /* --数据字典(简版):列名 类型 描述 set @att = select (select relname from pg_class where oid=a.attrelid) as table_name, '<tr><td>'||a.attname||'</td>' ||'<td>'||format_type(a.atttypid,a.atttypmod)||'</td>' ||'<td>'||col_description(a.attrelid,a.attnum)||'</td></tr>' 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 '<!DOCTYPE html>'; print '<html>'; print '<head>'; print '<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />'; print '<title>数据字典</title>'; print '<style type="text/css">'; print 'table { border-collapse: collapse; border-spacing: 0;}'; print 'table td {border: solid 1px #000;}'; print '</style>'; set @i=0; while @i < lines(@table) begin set @table_name = @table[@i][0]; print @table[@i][1]; print '<table>'; print '<tr><td>列名</td><td>类型</td><td>长度</td><td>主键约束</td><td>唯一约束</td><td>外键约束</td><td>可否为空</td><td>描述</td></tr>'; --print '<tr><td>列名</td><td>类型</td><td>描述</td></tr>'; set @j=0; while @j < lines(@att) begin if @att[@j][0] = @table_name begin print @att[@j][1]; end set @j=@j+1; end print '</table>'; 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; */

企业建站2800元起,携手武汉肥猫科技,做一个有见地的颜值派!更多优惠请戳:十堰网站制作 http://shiyan.666rj.com

上一篇:在python中实现基于ICE框架的client、server模型 下一篇:最后一页

相关阅读