获得元数据信息

mysql

-- 查询当前库有多少个表
select 
	count(*) 
from information_schema.tables 
where 
	table_schema = (select database())
	
-- 查询所有的表及其元数据信息
select 
	table_name tableName, 
	engine, 
	table_comment tableComment, 
	create_time createTime 
	from information_schema.tables 
where 
	table_schema = (select database()) 
	
-- 获得表的所有列的元数据信息
select 
	column_name columnName, 
	data_type dataType, 
	column_comment columnComment, 
	column_key columnKey, extra 
from information_schema.columns
where 
	table_name = 'xxx' 
	and table_schema = (select database()) 
order by ordinal_position

select 
	a.TABLE_SCHEMA
	,a.TABLE_NAME
	,a.TABLE_ROWS
	,a.TABLE_COMMENT
	,a.TABLE_TYPE
	,a.CREATE_TIME
	,b.*
from information_schema.`TABLES` a,
     information_schema.`COLUMNS` b
where a.TABLE_SCHEMA=b.TABLE_SCHEMA
and a.TABLE_NAME=b.TABLE_NAME


-- 获得表的引擎和注释
select 
	table_name tableName, 
	engine, 
	table_comment tableComment, 
	create_time createTime 
from information_schema.tables 
where 
	table_schema = (select database()) 
	and table_name = 'xxx' 

oracle

select 
	a.owner as TABLE_SCHEMA
	,a.table_name
	,a.num_rows as TABLE_ROWS
	,a.TABLE_COMMENT
	,a.TABLE_TYPE
	,a.CREATE_TIME
	,b.*
from (select 
		t1.*
		,t2.COMMENTS as TABLE_COMMENT
		,t2.TABLE_TYPE
		,t3.created as CREATE_TIME
	  from all_tables t1
	  left join all_tab_comments t2 on t1.owner=t2.owner and t1.TABLE_NAME=t2.TABLE_NAME
	  left join all_objects t3 on t1.owner=t3.owner and t1.TABLE_NAME=t3.object_name
	 ) a
,(select 
	t1.*
	,t2.COMMENTS as COLUMN_COMMENT
  from ALL_TAB_COLS t1
  left join ALL_COL_COMMENTS t2 on t1.owner=t2.owner and t1.TABLE_NAME=t2.TABLE_NAME and t1.COLUMN_NAME=t2.COLUMN_NAME
 ) b
where a.owner=b.owner
and a.TABLE_NAME=b.TABLE_NAME
and a.table_name = 'xxx'

postgresql/gp

postgresql比较特殊一点，因为postgresql使用了namespace的概念，可能同一个库中不同的namesespace有两个表明一样的表：

-- 查询某个命名空间的所有的表名
select tablename from pg_tables where schemaname = 'xxx'

-- 查询某个命名空间所有的表的注释信息
select 
 relname as "name",
 obj_description(oid) as "comment"
from pg_class 
where 
  obj_description(relnamespace) like '%xxx%'
-- 	and relname = 'xxx' -- 需要指定某个表的时候给表名的条件

-- 查询某个命名空间的某个表的所有列的元数据信息
-- 两个函数的用法参考官方文档：https://www.postgresql.org/docs/10/functions-info.html
-- 关于存储表和列元数据的信息文档：https://www.postgresql.org/docs/10/catalogs.html


select table_name,column_name,data_type,udt_name,character_maximum_length,numeric_precision,numeric_scale
from information_schema.columns t1
where table_schema = 'icl'
and table_name in 
('cm_indv_loan_contr_info_sum')
order by table_name,ordinal_position ;


select 
	col_description(pa.attrelid, pa.attnum) as "comment",
	format_type(pa.atttypid, pa.atttypmod) as "type",
	pa.attname as "name"
from pg_class as pc, pg_attribute as pa, pg_namespace as pn
where 
	pa.attrelid = pc.oid
	and pn.nspname = 'xxx'
	and pc.relname = 'xxx'
	and col_description(pa.attrelid, pa.attnum) is not null

select 
	a.schemaname
	,a.tablename   as TABLE_NAME 
	,b.reltuples   as TABLE_ROWS
	,c.description as TABLE_COMMENT
	,b.relkind     as TABLE_TYPE
	,d.attname 	   as column_name
	,e.typname     as column_type
    ,substring(format_type(d.atttypid,d.atttypmod) from '\(.*\)') as type_len
	,f.description as column_COMMENT
from pg_tables a
left join pg_class b on a.tablename=b.relname
left join (select * from pg_description where objsubid =0 ) c on b.oid = c.objoid
left join (select * from pg_attribute where attnum>0 )d on b.oid = d.attrelid
left join pg_type e on d.atttypid = e.oid
left join (select * from pg_description where objsubid <>0 ) f on b.oid = f.objoid and d.attnum = f.objsubid
left join pg_namespace g on b.relnamespace = g.oid 
where a.tablename = 'xxx'
and a.schemaname = 'xxx'
and g.nspname='xxx'
;

-- 获取分区信息
SELECT pg_get_partition_def('schemaname.tablename'::regclass,true);

参考：
postgresql文档：PostgreSQL: Documentation: 10: PostgreSQL 10.19 Documentation