PostgreSQL 根据注释搜字段和表名

最新推荐文章于 2024-10-12 15:13:48 发布

LaLaLa_OvO

最新推荐文章于 2024-10-12 15:13:48 发布

阅读量222

点赞数

分类专栏：数据库文章标签： postgresql 数据库 sql

本文链接：https://blog.youkuaiyun.com/qq56477643/article/details/129425154

版权

数据库专栏收录该内容

12 篇文章

订阅专栏

该SQL查询用于获取公共模式下数据库表的详细结构信息，特别是那些在列注释中包含状态字样的列。它包括表名、列名、数据类型、是否允许为空、是否为主键、列的最大长度、精度、默认值以及是否为自增列等关键信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select
	table_catalog ,
	table_schema ,
	table_name ,
	ordinal_position as sort,
	column_name ,
	data_type as TypeName,
	(case
		when (is_nullable = 'no' and contype !='p' ) then '1'
		else null
	end) as is_required,
	(case
		when contype = 'p' then '1'
		else '0'
	end) as is_pk,
	coalesce(character_maximum_length, numeric_precision,-1) as Length,
	numeric_scale as scale,
	case
		is_nullable when 'NO' then 0
		else 1
	end as canNull,
	column_default as defaultval,
	case
		when position('nextval' in column_default)>0 then 1
		else 0
	end as IsIdentity,
	(case
		when position('nextval' in column_default)>0 then 1
		else 0
	end) as is_increment,
	c.DeText as column_comment,
	c.typname as column_type,
	ordinal_position
from
	information_schema.columns
	left join (select
		datname,pg_get_userbyid(relowner) AS tableowner,nspname,relname,attname
	                , col_description(pg_attr.attrelid, pg_attr.attnum) as DeText,typname,pg_cons.contype
	from
		pg_class
	left join pg_attribute pg_attr on
		pg_attr.attrelid = pg_class.oid
	left join pg_description pg_desc on
		pg_desc.objoid = pg_attr.attrelid
		and pg_desc.objsubid = pg_attr.attnum
	left join pg_namespace pg_ns on
		pg_ns."oid" = pg_class.relnamespace
	left join pg_database on relowner = datdba
	left join pg_type on pg_attr.atttypid = pg_type."oid"
	left join (select pg_con.*,unnest(conkey) conkey_new from pg_constraint pg_con)  pg_cons on
		pg_attr.attrelid = pg_class.oid
		and pg_attr.attnum = pg_cons.conkey_new and pg_cons.conrelid = pg_class.oid
	where
		pg_attr.attnum>0
		and pg_attr.attrelid = pg_class.oid
		 and nspname='public'
		) c
	on  table_schema = nspname and table_name = relname and column_name = attname
where table_schema='public' and c.DeText like '%状态%'
order by table_name