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;

转载：