Skip to Content
Author's profile photo Jerry Wang

Another approach to get the number of records stored in a table in HANA DB

Still used the old way SELECT COUNT(*) to get total number of records stored in a table?
If you are using HANA database, there is another approach to achieve the same.
In HANA there is a metadata table m_tables which stores the related information:
You can find its definition in SAP help.
You can use the following ABAP code to access this HANA table from ABAP server:
class CL_CRM_HOME_TABLE_SIZE_TOOL definition
  create public .

public section.

  TYPES: BEGIN OF ty_size,
         table_name   TYPE char256,
         record_count TYPE int4,
         table_size   TYPE int4,
       END OF ty_size.

TYPES: tt_size TYPE TABLE OF ty_size with key table_name.

  class-methods GET_SIZE
      value(RT_RESULT) type tt_size .
protected section.
private section.

  METHOD get_size.
    DATA(lv_in) = REDUCE string(  INIT x TYPE string FOR <data> IN it_input NEXT x = SWITCH #( x
        WHEN space THEN |'{ <data> }'|  ELSE x && ',' && |'{ <data> }'| ) ).

        DATA(lo_sql_con) = cl_sql_connection=>get_connection(  ).
        DATA(lo_stmt) = lo_sql_con->create_statement( ).
        DATA: lv_stmt TYPE string.

        lv_stmt = |select table_name, record_count, table_size from m_tables where table_name in ({ lv_in })|.

        DATA(lo_res) = lo_stmt->execute_query( lv_stmt  ).

        GET REFERENCE OF rt_result INTO DATA(lr_data).
        lo_res->set_param_table( lr_data ).
        lo_res->next_package( ).
        lo_res->close( ).
      CATCH cx_sql_exception INTO DATA(cx_root).
        WRITE:/ 'Error:', cx_root->get_text( ).

Consumer report:

REPORT zsize.

DATA: lt_input TYPE String_table.

lt_input = VALUE #( ( CONV string( 'TADIR' ) )
                    ( CONV string( 'TFDIR' ) )

DATA(lt_size) = cl_crm_home_table_size_tool=>get_size( lt_input ).

cl_demo_output=>display_data( lt_size ).


Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Umar Abdullah
      Umar Abdullah

      Hi   Jerry,

      is CL_CRM_HOME_TABLE_SIZE_TOOL  a customer class or provided by SAP.

      Author's profile photo Jerry Wang
      Jerry Wang
      Blog Post Author

      Hi Umar,

      It is a customer class.

      Best regards,


      Author's profile photo Lars Breddemann
      Lars Breddemann

      Hi Jerry

      why would you want to use this approach on SAP HANA instead of the SELECT count(*)?

      By querying the system view you silently ignore the separation of clients in the system and you might see a higher record count than any application in the current client.

      Performance-wise I have not seen a difference between both approaches and adding the knowledge of a SAP HANA system table to your application just to get the number of records seems like a lot of effort to me.



      Author's profile photo Jerry Wang
      Jerry Wang
      Blog Post Author

      Hi Lars,

      That's very kind of you: thanks a lot to point out that this approach will fetch the data from all clients in the server - I never knew this before!

      Regarding the reason why I use this approach instead of SELECT count(*): well since this is an tool developed and only used by me to improve my daily work efficiency, so I always tend to try new approach to learn something new 🙂

      Best regards,