CODEWITHSUNDEEP
javasql-serverdatabaseoracle-databasejdbc
Chanky Mallick
Chanky Mallick

Reputation: 587

How to get Column Comments in JDBC

I want to fetch Column comments using JDBC Metadata , But everytime it returns null , I tested with Oracle and SqlServer both cases it returning Null.

DatabaseMetaData dmt = con.getMetaData();    
colRs = dmt.getColumns(null, "dbo", 'Student', null);
while (colRs.next()) {
System.out.println(colRs.getString("REMARKS");
   }

While i am getting all other data like column name , length etc absolutely ok ...

Upvotes: 3

Views: 5486

Answers (2)

fall
fall

Reputation: 1202

The issue is that the getColumns() method from DatabaseMetaData does not retrieve the column comments/remarks by default for Oracle database. To fetch column comments in Oracle, you need to query the ALL_COL_COMMENTS view:

  // Query comments 
  String sql = "SELECT column_name, comments FROM all_col_comments WHERE table_name = ?";
  PreparedStatement ps = connection.prepareStatement(sql);
  ps.setString(1, tableName);

  ResultSet rs2 = ps.executeQuery();
  while (rs2.next()) {
    String name = rs2.getString("column_name");
    String comment = rs2.getString("comments");
  }

Upvotes: 0

user330315
user330315

Reputation:

For Oracle you need to provide a connection property remarksReporting and set that to true or call the method setRemarksReporting() to enable that. 

OracleConnection oraCon = (OracleConnection)con;
oraCon.setRemarksReporting(true);

After that, getColumns() will return the column (or table) comments in the REMARKS column of the ResultSet.

See Oracle's JDBC Reference for more details

For SQL Server this is not possible at all.

Neither the Microsoft nor the jTDS driver expose table or column comments. Probably because there is no SQL support for that in SQL Server. The usual approach of using "extended properties" and the property name MS_DESCRIPTION is not reliable. Mainly because there is no requirement to us MS_DESCRIPTION as the property name. Not even sp_help returns those remarks. And at least the jTDS driver simply calls sp_help go the the table columns. I don't know what the Microsoft driver does.

The only option you have there, is to use fn_listextendedproperty() to retrieve the comments:

e.g.:

SELECT objname, cast(value as varchar(8000)) as value 
FROM fn_listextendedproperty ('MS_DESCRIPTION','schema', 'dbo', 'table', 'Student', 'column', null)

You need to replace MS_DESCRIPTION with whatever property name you use to store your comments.

Upvotes: 8

Related Questions