Technical Articles
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!