PostgreSQL查询表以及字段的备注

查询所有表名称以及字段含义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.attt

路大师_XA

3796人浏览 · 2021-09-06 14:40:45

路大师_XA · 2021-09-06 14:40:45 发布

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

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

查看所有表名

SELECT
	tablename 
FROM
	pg_tables 
WHERE
	schemaname = 'public' 
	AND POSITION ( '_2' IN tablename ) = 0;
	
	
SELECT
	* 
FROM
	pg_tables;

查看表名和备注

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;

查看特定表名备注

SELECT
	relname AS tabname,
	CAST ( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS COMMENT 
FROM
	pg_class C 
WHERE
	relname = '表名';

查看特定表名字段

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 = '表名' 
	AND A.attnum > 0 
	AND A.attrelid = C.oid 
	AND A.atttypid = T.oid 
	AND d.objoid = A.attrelid 
	AND d.objsubid = A.attnum;

转载：https://www.cnblogs.com/ygzone/p/10677297.html#%E6%9F%A5%E8%AF%A2%E6%89%80%E6%9C%89%E8%A1%A8%E5%90%8D%E7%A7%B0%E4%BB%A5%E5%8F%8A%E5%AD%97%E6%AE%B5%E5%90%AB%E4%B9%89