倚栏望海集

select tbl.relname as TableName,
col.attname as ColumnName,
pg_type.typname as ColumnType,
(case when col.attlen<0 then col.atttypmod else col.attlen end) as ColumnLen,
(select count(*) from pg_constraint ct where ct.contype=p::char and col.attnum = any (conkey) and ct.conrelid=tbl.oid) as IsPk,
(case when seq.oid is null then 0 else 1 end) as IsAutoIncrease,
(case when col.attnotnull=false then 1 else 0 end) as AllowNullable
from pg_attribute col
inner join pg_class tbl on col.attrelid=tbl.oid and tbl.relkind=r::char
left join pg_depend dp on tbl.oid=dp.refobjid and col.attnum=dp.refobjsubid and deptype=i::char
left join pg_class seq on dp.objid=seq.oid and seq.relkind=S::char
inner join pg_namespace space on tbl.relnamespace=space.oid and space.nspname<>pg_catalog::name and space.nspname<>information_schema::name and space.nspname<>pg_toast::name
left join pg_type on col.atttypid=pg_type.oid 
where col.attnum>0 
order by tbl.relname, col.attnum