1、查询表字段和注释sql
/**
* 查询表字段和注释
* @param tableName {表名}
* @param schema {数据库对象}
* @param databaseType {shujukuleixing }
* @return sql
*/
public static String getTableColumns(String tableName, String schema, String databaseType){
if (StringUtils.isEmpty(tableName) || StringUtils.isEmpty(schema) || StringUtils.isEmpty(databaseType)) {
return null;
}
//查询表字段和注释sql
String sql = "";
if ("ORACLE".equals(databaseType.toUpperCase(Locale.ROOT))) {
sql = "SELECT T.COLUMN_NAME AS COLNAME ,T.DATA_LENGTH AS LENGTH,C.COMMENTS AS REMARKS FROM USER_TAB_COLUMNS T INNER JOIN USER_COL_COMMENTS C ON C.COLUMN_NAME=T.COLUMN_NAME and C.TABLE_NAME = T.TABLE_NAME WHERE C.TABLE_NAME='" + tableName + "'";
} else if ("DB2".equals(databaseType.toUpperCase(Locale.ROOT))) {
//"SCALE"
sql = "SELECT COLNAME,LENGTH,REMARKS FROM SYSCAT.COLUMNS WHERE TABNAME='" + tableName + "' AND TABSCHEMA='" + schema + "'";
} else if ("MYSQL".equals(databaseType.toUpperCase(Locale.ROOT))) {
// TIPS MySQL分组查询需要sql_mode移除ONLY_FULL_GROUP_BY，可以通过下面的语句修改
// SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
sql = "SELECT COLUMN_NAME AS COLNAME, ( IFNULL( NUMERIC_PRECISION, 0 ) + IFNULL(CHARACTER_MAXIMUM_LENGTH,0) ) AS LENGTH, COLUMN_COMMENT AS REMARKS FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA ='" + schema + "'AND TABLE_NAME = '" + tableName + "'";
}
return sql;
}
