MySQL PostgresQL查询所有表结构

最新推荐文章于 2024-07-25 11:35:55 发布

为啥我就不胖呢

最新推荐文章于 2024-07-25 11:35:55 发布

阅读量418

点赞数

分类专栏：日常小工具文章标签： postgresql mysql 数据库

本文链接：https://blog.csdn.net/chenpengjia006/article/details/121703369

版权

日常小工具专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL

查询表名及注释

select table_name as name,table_comment as remarks from information_schema.tables 
where table_schema= 表空间 and table_type='base table'

查询表对应列

select table_name as tableName,column_name as name,ordinal_position as sort,is_nullable as isNullable,column_type as type ,
column_key as columnKey,column_comment as comment,column_default as columnDefault from 
information_schema.columns where table_schema= 表空间

PostgresQL

查询报名、注释以及OID

SELECT a.oid,
       a.relname AS name,
       b.description AS remarks
  FROM pg_class a
       LEFT OUTER JOIN pg_description b ON b.objsubid=0 AND a.oid = b.objoid
 WHERE a.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname= 模式名 ) 
   AND a.relkind='r'
 ORDER BY a.relname

查询所有列根据OID关联

SELECT 
 A.attnum as sort,
	( SELECT description FROM pg_catalog.pg_description WHERE objoid = A.attrelid AND objsubid = A.attnum ) AS comment,
	A.attname as name,
	( select typname from pg_type where oid = A.atttypid) AS type,
	A.atttypmod AS data_type,
	attlen,case when attnotnull then '否' else '是' end as isNullable,atthasdef
FROM
	pg_catalog.pg_attribute A
WHERE
	1 = 1 
	AND A.attrelid = 此处填入OID 
	AND A.attnum > 0 
	AND NOT A.attisdropped 
ORDER BY
	A.attnum

查询主键

SELECT
	  pg_class.oid,
    pg_attribute.attname AS colName
FROM
    pg_constraint
INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
INNER JOIN pg_attribute ON pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attnum = pg_constraint.conkey [1]
INNER JOIN pg_type ON pg_type.oid = pg_attribute.atttypid
WHERE
   pg_constraint.contype = 'p' order by oid