Recently someone in my team asked me how to fetch column list along with datatype from VIEW definition via jdbc connection. I proposed couple of solutions to him however due to existing code base he was looking for solution using “HELP” command only. Now if you know there are multiple HELP command which can give insight into object details. For more details you can read this post- Teradata HELP commands. However for views you may get column names but datatype may be missing.

Since the expected solution is via HELP command only, I proposed to create a volatile table from view and use HELP TABLE on it to get the details. This solved the problem and solution fits in the existing code base. Let’s look into it via a quick example

help table warehouse;
replace view warehouse_v as select * from warehouse;
help view warehouse_v;

You can see only the column names are present. Other info like datatype etc are not available. Even if you try using dbc.columns you will not get this info for VIEW. Let’s see our solution now

create volatile table vt_warehouse as (select * from warehouse_v) with no data;
help table vt_warehouse;

So we can get the info now on Column Names, Data types. One point to observe here is it has converted all columns to NULL and the reason behind it the CTAS command we have used to create volatile table.

Leave a Reply

Your email address will not be published. Required fields are marked *