HANA’s default internal columns per tables

Topic: What is the difference between M_CS_COLUMNS and M_CS_ALL_COLUMNS:

Like many other HANA DB maintained HANA system view, HANA also maintains the views M_CS_COLUMNS and M_CS_ALL_COLUMNS which hold list of columns that are present in any table in HANA. But why HANA should have 2 different views for same details ? How are these 2 tables different ?

The difference can be found with the help of below SQL by checking the number of entries that has got displayed.

select * from M_CS_COLUMNS where TABLE_NAME = ‘<TABLE NAME>’ and SCHEMA_NAME=’<SCHEMA_NAME>’;

select * from M_CS_ALL_COLUMNS where TABLE_NAME = ‘<TABLE NAME>’ and SCHEMA_NAME=’<SCHEMA_NAME>’;

NOTE : If the table that is queried is partitioned, change the above sql as “select distinct COLUMN_NAME from …….“

The number of columns that are displayed by above 2 queries will be different . If output of M_CS_COLUMNS gives n rows, M_CS_ALL_COLUMNS will give n+3 columns .

Answer:

When ever a table is created in HANA, hana internally adds its own 3 columns to HANA internal purposes which we generally do not use in normal queries and also does not it gets displayed with normal “select * from <table_name>” query. The system view M_CS_ALL_COLUMNS will have these additional columns .

The above specified columns are HANA internal columns that can be seen only in M_CS_ALL_COLUMNS . We can see these tables in many HANA related error messages .

Other use cases which is strictly prohibited in production operations :

Eg: delete from SAPERP.BSPL_test where “$rowid$” < 1000;  In this sql, all the top 1000 tables entries will be deleted and this is not possible unless we use this internal column . This is like index number for each HANA column.

Other possible clauses are below.

Thanks for reading!
Follow for more such posts! Click on FOLLOW =>https://people.sap.com/rajarajeswari_kaliyaperumal
Like and leave a comment or suggestion if any!