我是标题
最近在做数据集成系统,遇到各个数据库不同的sql处理真是一个脑壳两个大,好歹挺过来了,记录一下麻烦而又没有什么技术难度的问题
Oracle:
SELECT *
FROM user_tab_columns t
INNER JOIN user_cons_columns ucc ON t.COLUMN_NAME = ucc.COLUMN_NAME AND t.TABLE_NAME = ucc.TABLE_NAME
INNER JOIN user_constraints uc ON ucc.CONSTRAINT_NAME = uc.CONSTRAINT_NAME AND ucc.TABLE_NAME = uc.TABLE_NAME
WHERE t.TABLE_NAME IN ('CCC') AND uc.CONSTRAINT_TYPE = 'P'
- user_tab_columns 存的是字段基本信息,包括表名、字段名、数据类型、长度
- user_constraints 存的是表中设有的约束,例如:主键约束、唯一索引等
- user_cons_columns 存的是表中的约束列,用CONSTRAINT_TYPE = 'P’则是主键
MySql:
SELECT *
FROM information_schema.`COLUMNS` t
WHERE t.TABLE_SCHEMA = 'TEST' AND t.TABLE_NAME IN ('CCC') AND t.COLUMN_KEY = 'PRI'
- MySql的比较简单,查询MySql系统库information_schema的COLUMNS里面包含字段信息以及COLUMN_KEY = 'PRI’为主键
Sap Hana:
SELECT *
FROM TABLE_COLUMNS t
INNER JOIN INDEX_COLUMNS c ON t.COLUMN_NAME = c.COLUMN_NAME AND t.SCHEMA_NAME = c.SCHEMA_NAME AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.SCHEMA_NAME = 'DBO' AND t.TABLE_NAME IN ('FDI_JOB_1') AND c.CONSTRAINT = 'PRIMARY KEY'
- TABLE_COLUMNS 存的是字段基本信息,包括表名、字段名、数据类型、长度
- INDEX_COLUMNS 存的是表里约束信息,包括表名、字段名,CONSTRAINT = 'PRIMARY KEY’可以得到主键
Sql Server
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS t
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON t.TABLE_CATALOG = tc.TABLE_CATALOG
AND t.TABLE_SCHEMA = tc.TABLE_SCHEMA AND t.TABLE_NAME = tc.TABLE_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON t.TABLE_CATALOG = kcu.TABLE_CATALOG
AND t.TABLE_SCHEMA = kcu.TABLE_SCHEMA AND t.TABLE_NAME = kcu.TABLE_NAME
AND t.COLUMN_NAME = kcu.COLUMN_NAME AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE t.TABLE_NAME IN ('fdi_job_1') AND tc.CONSTRAINT_TYPE= 'PRIMARY KEY'
- SqlServer的查询都来源于系统的识图
- INFORMATION_SCHEMA.COLUMNS 查询字段基本信息
- INFORMATION_SCHEMA.TABLE_CONSTRAINTS 查询表约束、约束名称及约束类型
- INFORMATION_SCHEMA.KEY_COLUMN_USAGE 查询表约束名称及约束字段