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.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