突然接到领导派发的一个需求,现场需要一份当前某个模块下的业务表结构设计说明。需要提供的信息如下:表名、字段名称、字段类型长度、字段备注等信息。
SELECT c.table_name,
c.column_name,
c.data_type,
c.character_maximum_length,
c.is_nullable,
c.column_default,
CASE
WHEN kcu.column_name IS NOT NULL THEN 'PRIMARY KEY'
ELSE ''
END AS column_key,
pgd.description AS column_comment
FROM information_schema.columns c
LEFT JOIN information_schema.key_column_usage kcu ON c.table_schema = kcu.table_schema
AND c.table_name = kcu.table_name
AND c.column_name = kcu.column_name
LEFT JOIN pg_catalog.pg_description pgd
ON pgd.objoid = (
SELECT d.oid
FROM pg_catalog.pg_class d
WHERE d.relname = c.table_name
AND d.relnamespace = (SELECT n.oid FROM pg_catalog.pg_namespace n WHERE n.nspname = c.table_schema) )
AND pgd.objsubid = c.ordinal_position
WHERE
c.table_schema = 'public'
ORDER BY
c.table_name, c.ordinal_position;
最终的效果如下