Oracle查询主键、描述表信息及mysql查询字段信息 SQL

Oracle查询主键、描述表信息及mysql查询字段信息 SQL

MYSQL

mysql查询字段信息

show full fields from db_name.table_name;

Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
unit_id	int(11)		NO	PRI		auto_increment	select	组织ID
unit_code	varchar(28)	utf8_bin	YES	MUL			select	组织编码
unit_level_name	varchar(8)	utf8_bin	YES			select	组织级别名称

判断数据库是否存在

show databases like 'db_name';

判断数据表是否存在

show tables like 'table_name'; 

Oracle

Oracle 查询主键

SELECT
	col.column_name 
FROM
	dba_constraints con,
	dba_cons_columns col 
WHERE
	con.constraint_name = col.constraint_name 
	AND con.constraint_type = 'P' 
	AND col.table_name = 'xxx'

Oracle 描述表信息(字段顺序与原库一致)

SELECT
	t1.COLUMN_NAME,
	t1.DATA_TYPE,
	t1.DATA_PRECISION,
	t1.DATA_SCALE 
FROM
	dba_tab_columns t1
	LEFT JOIN dba_col_comments t2 ON t1.COLUMN_NAME = t2.COLUMN_NAME 
WHERE
	t1.OWNER = 'xxx' 
	AND t1.TABLE_NAME = 'xxx' 
	AND t2.OWNER = 'xxx' 
	AND t2.TABLE_NAME = 'xxx' 
ORDER BY
	COLUMN_ID

---

Shylin