How to get sap hana table description using TABLE_COLUMNS view?
Sap hana table description will be needed in many situations. Without a table column description, data modeling and writing queries are almost impossible. It is possible to obtain a sap hana table lay out in different ways. The following example requires a database access to system views and more particularly the TABLE_COLUMNS view. The query is simple and the result is all what is required for modelling and data query.
The following query can be run either with hana studio SQL console or with hdbsql. If you change and run the query with hdbsql, you have possibilities, via shell script, to build up more sophisticated results.
-- Show table column definition
SELECT COLUMN_NAME, DATA_TYPE_NAME||'('||LENGTH||']' DataType, is_nullable, index_type
WHERE TABLE_NAME = 'A_COLTABLE'
AND SCHEMA_NAME = 'PROJ_MANAG_SCHEMA'
order by row_order_position;
| COLUMN_NAME | DATATYPE | IS_NU | INDE |
| ----------- | ------------ | ----- | ---- |
| TB_DATEIN | DATE(10] | TRUE | NONE |
| TB_ID | NVARCHAR(10] | FALSE | FULL |
| TB_IDX_NAME | NVARCHAR(20] | TRUE | NONE |
| TB_LINKTO | VARCHAR(20] | TRUE | NONE |
| TB_NAME | NVARCHAR(20] | TRUE | NONE |
| TB_SIZE_KB | INTEGER(10] | TRUE | NONE |