从mysql和oracle获取表的字段信息（主键，注释，长度）

-- mysql
-- 获取某个数据库下所有表注释，TABLE_SCHEMA即数据库名
select table_schema,table_name,table_comment from information_schema.TABLES where TABLE_SCHEMA = ?;
-- 例如
select table_schema,table_name,table_comment from information_schema.TABLES where TABLE_SCHEMA = 'mydb';

-- 获取某张表的字段信息
select * from information_schema.COLUMNS where TABLE_SCHEMA = ? and TABLE_NAME = ?;
-- 例如
select * from information_schema.COLUMNS where TABLE_SCHEMA = 'mydb' and TABLE_NAME = 'student';


-- oracle
-- 获取当前用户下所有表的注释
select * from user_tab_comments;
-- 获取某张表的字段信息(constraint_type='P'意为主键)
SELECT
	* 
FROM
	user_tab_columns u1
	LEFT JOIN (
	SELECT
		c.table_name tn,
		c.column_name cn,
		c.comments,
		k.CONSTRAINT_TYPE 
	FROM
		user_col_comments c
		LEFT JOIN (
		SELECT
			t1.table_name tn,
			t1.COLUMN_NAME cn,
			t2.constraint_type 
		FROM
			user_cons_columns t1, user_constraints t2 
		WHERE
			t1.constraint_name = t2.constraint_name 
			AND t2.constraint_type = 'P' 
		) k ON c.table_name = k.tn 
		AND c.column_name = k.cn 
	) u2 ON u1.TABLE_NAME = u2.tn 
	AND u1.COLUMN_NAME = u2.cn 
WHERE
	u1.table_name = 'PERSON';