oracle 查询表里信息_ORACLE 查询表基本信息

1.相关表字段

USER_COL_COMMENTS  字段基本信息表

USER_TAB_COLUMNS  字段详情表

SELECT T.TABLE_NAME,

T.COLUMN_NAME,

C.DATA_TYPE,

C.DATA_LENGTH,

C.NULLABLE,

T.COMMENTS

FROM USER_COL_COMMENTS T, USER_TAB_COLUMNS C

WHERE C.COLUMN_NAME = T.COLUMN_NAME

AND C.TABLE_NAME = 'MC_ARTICLE'

AND C.TABLE_NAME = T.TABLE_NAME

2.查询结果

3.java代码示例

public static List> getTableInfo(String tableName) throws Exception {

Connection conn = getConnection(driver, url, userName, password);

Statement stat = conn.createStatement();

String sql = "SELECT T.TABLE_NAME,T.COLUMN_NAME,C.DATA_TYPE,C.DATA_LENGTH,C.NULLABLE,T.COMMENTS FROM USER_COL_COMMENTS T, USER_TAB_COLUMNS C WHERE C.COLUMN_NAME = T.COLUMN_NAME AND C.TABLE_NAME = '"+tableName+"' AND C.TABLE_NAME = T.TABLE_NAME";

ResultSet res = stat.executeQuery(sql);

List> list =new ArrayList>();

while(res.next()) {

Map map = new LinkedHashMap();

map.put("name", res.getString("COMMENTS"));

map.put("columnName", res.getString("COLUMN_NAME"));

map.put("columnType", res.getString("DATA_TYPE"));

map.put("length", res.getString("DATA_LENGTH"));

map.put("isNull", res.getString("NULLABLE").equals("Y")?"NULL":"NOT NULL");

list.add(map);

}

stat.close();

conn.close();

return list;

}