sqlplus查看表结构_一文总结PG查看表结构、索引、主键、外键、触发器常用SQL

概述

今天主要简单总结下PG数据库平时去查看表结构、索引、主键、外键、触发器的一些SQL，仅供参考。

---

一、查看XX表结构

 d tablename  

或者如下：

SELECT A.attnum,A.attname AS field,T.typname AS TYPE,A.attlen AS LENGTH,A.atttypmod AS lengthvar,A.attnotnull AS NOTNULL,b.description AS COMMENT FROMpg_class C,pg_attributeA LEFT OUTER JOIN pg_description b ON A.attrelid = b.objoid AND A.attnum = b.objsubid,pg_type T WHEREC.relname = 'pg_stat_database' AND A.attnum > 0 AND A.attrelid = C.oid AND A.atttypid = T.oid ORDER BYA.attnum;

---

二、查看表索引

SELECT A.SCHEMANAME,A.TABLENAME,A.INDEXNAME,A.TABLESPACE,A.INDEXDEF,B.AMNAME,C.INDEXRELID,C.INDNATTS,C.INDISUNIQUE,C.INDISPRIMARY,C.INDISCLUSTERED,D.DESCRIPTION FROMPG_AM BLEFT JOIN PG_CLASS F ON B.OID = F.RELAMLEFT JOIN PG_STAT_ALL_INDEXES E ON F.OID = E.INDEXRELIDLEFT JOIN PG_INDEX C ON E.INDEXRELID = C.INDEXRELIDLEFT OUTER JOIN PG_DESCRIPTION D ON C.INDEXRELID = D.OBJOID,PG_INDEXES A WHEREA.SCHEMANAME = E.SCHEMANAME AND A.TABLENAME = E.RELNAME AND A.INDEXNAME = E.INDEXRELNAME AND E.SCHEMANAME = 'public' --and E.RELNAME = 't_student';--或者直接查系统视图select * from pg_indexes

---

三、查看表主键

--查询主键名称select c.relname,p.conname from pg_constraint p inner join pg_class c on p.conrelid=c.oid where p.contype='p'and c.relname='t_bdt_budget_d'--查询主键的详细信息SELECT C.relname,P.conname,A.attname,T.typname FROMpg_constraintP INNER JOIN pg_class C ON P.conrelid = C.oidINNER JOIN pg_attribute A ON A.attrelid = C.oid AND A.attnum = P.conkey [ 1 ]INNER JOIN pg_type T ON T.oid = A.atttypid WHEREP.contype = 'p' AND C.relname = 't_bdt_budget_d'

---

四、查看表外键

-- 查看当前表IDSELECT oid, relname FROM pg_class WHERE relname = 'syslogfilter';-- 查看引用当前表ID作参考表的主外键约束信息SELECT * FROM pg_CONSTRAINT WHERE confrelid = '24935';-- 查看那些外键的名称SELECT oid, relname FROM pg_class WHERE oid in(SELECT conrelid FROM pg_CONSTRAINT WHERE confrelid = '24935');

---

五、查看触发器

dy：查看触发器

--当前数据库所有的触发器SELECT * FROM pg_trigger--特定表的触发器SELECT *  FROM pg_trigger t, pg_class  c WHERE t.tgrelid=c.oid AND c.relname='company';

---

六、查看视图

dv: 查看所有自己创建的视图
dv+: 查看所有自己创建的视图，显示大小

 select * from pg_views

---

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~