赞
踩
SELECT
relname AS tabname,
cast( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS COMMENT
FROM
pg_class c
WHERE
relkind = 'r'
AND relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'sql_%'
ORDER BY
relname
注意:过滤掉分表:加条件 and relchecks=0 即可
SELECT
col_description ( a.attrelid, a.attnum ) AS COMMENT,
format_type ( a.atttypid, a.atttypmod ) AS type,
a.attname AS NAME,
a.attnotnull AS notnull
FROM
pg_class AS c,
pg_attribute AS a
WHERE
c.relname = '表名'
AND a.attrelid = c.oid
AND a.attnum >0
注意:如果直接复制spl去运行的话,如果不能运行,请把引号改英文的。
;
with tmp_tab as ( select pc.oid as ooid,pn.nspname,pc.* from pg_class pc left outer join pg_namespace pn on pc.relnamespace = pn.oid where 1=1 and pc.relkind in ('r') and pc.relnamespace = 2200 -- select pn.oid, pn.* from pg_namespace pn where 1=1 and pc.oid not in ( select inhrelid from pg_inherits ) and pc.relname not like '%peiyb%' order by pc.relname ),tmp_desc as ( select pd.* from pg_description pd where 1=1 and pd.objsubid = 0 --and pd.objoid=168605 ) select t0.* from ( select tab.nspname, tab.relname, de.description, 'comment on table '||tab.nspname||'.'||tab.relname||' is '''||de.description||''';' from tmp_tab tab left outer join tmp_desc de on tab.ooid = de.objoid where 1=1 ) t0 where 1=1 and t0.description is not null order by t0.relname ;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。