1、查詢表字段和注釋sql

/**
     * 查詢表字段和注釋
     * @param tableName {表名}
     * @param schema {數(shù)據(jù)庫對象}
     * @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;
    }