postgres 查询某模式下，所有表的名称及字段信息

1.postgres 查询某模式下，所有表的名称及字段信息

 

 with res_table as (
 
 
SELECT
	"table_name",obj_description ( oid, 'pg_class' ) 
FROM
	information_schema.tables t1, pg_class t2 
WHERE
	table_schema = '模式名称' 
	AND t1."table_name" = t2.relname
   

)
 

SELECT res_table.*, a.attname as 属性名称,col_description(a.attrelid,a.attnum) as 属性编码,format_type(a.atttypid,a.atttypmod) as 数据类型 ,
case  a.attnotnull   when   't'   then   '是'    else   '否'   end   as   是否非空 
FROM pg_class as c,pg_attribute as a ,res_table
where c.relname = res_table.table_name and a.attrelid = c.oid and a.attnum>0  ORDER BY res_table.table_name

去除空格及回车

 
 with res_table as (
 

SELECT
	"table_name",replace(replace(trim(obj_description ( oid, 'pg_class' ) ) ,chr(13),''),chr(10),'')    as table_ms
FROM
	information_schema.tables t1, pg_class t2 
WHERE
	table_schema = 'gwxm_result' 
	AND t1."table_name" = t2.relname
   

)
 

SELECT res_table.*, a.attname as 属性编码,replace(replace(trim(col_description(a.attrelid,a.attnum)) ,chr(13),''),chr(10),'')


 as 属性描述,format_type(a.atttypid,a.atttypmod) as 数据类型 ,
case  a.attnotnull   when   't'   then   '是'    else   '否'   end   as   是否非空 
FROM pg_class as c,pg_attribute as a ,res_table
where c.relname = res_table.table_name and a.attrelid = c.oid and a.attnum>0   ORDER BY res_table.table_name 

 

 

 

2.查某表的字段名称、数据类型、数据长度、注释

 
SELECT
	upper(A.attname) AS NAME ,
	upper(format_type ( A.atttypid, A.atttypmod )) AS TYPE,
	(case
when atttypmod-4>0 then atttypmod-4
else 0
end) zdcd,
upper(col_description ( A.attrelid, A.attnum )) AS COMMENT
	
FROM
	pg_class AS C,
	pg_attribute AS A 
WHERE
	C.relname = 't_ztzdyj_zdyjjsgzss' 
	AND A.attrelid = C.oid 
	AND A.attnum > 0
	GROUP BY COMMENT,TYPE,NAME,zdcd
	ORDER BY COMMENT desc,type desc