PostgreSQL查询数据库表以及字段备注

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

yghandsome

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

阅读量5.1k

点赞数 6

分类专栏： PostgreSQL 文章标签： PostgreSQL 数据库表备注

本文链接：https://blog.youkuaiyun.com/yghandsome/article/details/89141036

版权

PostgreSQL 专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍了使用SQL查询数据库的技巧，包括如何查询所有表名称及字段含义、查看所有表名、查看表名和备注、查看特定表名备注以及查看特定表名字段的方法。通过这些技巧，你可以更高效地管理和理解数据库结构。

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

文章目录

1、查询所有表名称以及字段含义

select c.relname 表名,cast(obj_description(relfilenode,'pg_class') as varchar) 名称,a.attname 字段,d.description 字段备注,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as 列类型 from pg_class c,pg_attribute a,pg_type t,pg_description d
where a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum
and c.relname in (select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0) order by c.relname,a.attnum

2、查看所有表名

select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0;

select * from pg_tables;

3、查看表名和备注

select relname as tabname,cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c
where relname in (select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0);

select * from pg_class;

4、查看特定表名备注

select relname as tabname,
cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c 
where relname ='tbl_alarm';

5、查看特定表名字段

select a.attnum,a.attname,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as type,d.description from pg_class c,pg_attribute a,pg_type t,pg_description d
where c.relname='tbl_alarm' and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum;