select
DISTINCT ORDINAL_POSITION as rn,
table_name as "tb_name",
column_name as "en_name",
(case udt_name WHEN 'numeric' THEN 'number' WHEN 'decimal' THEN 'number' WHEN 'int4' THEN 'number' WHEN 'int8' THEN'number'WHEN 'timestamp' THEN 'datetime' WHEN 'date' THEN 'datetime' WHEN 'datetime' THEN 'datetime' ELSE 'string' END ) as "data_type",coalesce(character_maximum_length,numeric_precision,-1) as "data_length",
coalesce(numeric_scale,0) as "precision_length",
case when position('nextval' in column_default)>0 then '1' else '0' end as "is_identity",
case when b.pk_name is null then 0 else '1' end as "is_pk",
case is_nullable when 'NO' then '0' else '1' end as "is_null",
c.DeText as remarks,
b.description as description ,
COLUMN_DEFAULT as "default_value"
from information_schema.columns
left join (
--B.description as description,
SELECT DISTINCT A.table_name as pk_table_name,B.attname as colname,B.attnum as attnum,
B.description as description ,
CASE WHEN length(B.attname) >0 THEN 1 ELSE NULL END AS pk_name
FROM information_schema.columns A
LEFT JOIN(
SELECT pg_attribute.attname, pg_attribute.attnum,pg_description.description as description
FROM pg_index, pg_class, pg_attribute ,pg_description
WHERE 1=1
AND pg_index.indrelid = pg_class.oid
AND pg_attribute.attrelid = pg_class.oid
and pg_description.objoid=pg_attribute.attrelid and pg_description.objsubid=pg_attribute.attnum
AND pg_attribute.attnum = ANY (pg_index.indkey)
)B ON A.column_name = b.attname
WHERE A.table_schema = current_schema()
and is_nullable='NO'
) b on b.colname = information_schema.columns.column_name and b.pk_table_name = information_schema.columns.table_name
left join (
SELECT col_description(a.attrelid,a.attnum) as DeText,a.attname as attname, c.relname as tbname,
c.relname as relname
FROM pg_class as c,pg_attribute as a where a.attrelid = c.oid and a.attnum>0
)c on c.attname = information_schema.columns. column_name and c.relname=information_schema.columns.table_name
where table_schema=current_schema()
order by table_name,rn asc
posted on
