Search code examples
javasql-serverdatabaseoracle-databasejdbc

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 ...

Solution

  • 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.