SAPFree generatorPDF, Excel, Word exportSAP S/4HANAAll SAP S/4HANA (on-premise) tools

SAP HANA Largest Tables SQL Query Generator

Smaller databases convert faster and cost less on HANA. This generator creates SQL that lists your biggest tables, so you can decide what to archive or delete before migration.

Fill in

Schema name

Often SAPHANADB or SAP<SID>

Number of tables

Minimum row count

Generated code

Free, optional. Reopen it on any device.

Download:

Largest tables by disk size

SELECT TOP 50
       SCHEMA_NAME,
       TABLE_NAME,
       RECORD_COUNT,
       ROUND(TABLE_SIZE / 1024 / 1024 / 1024, 2) AS SIZE_GB
FROM SYS.M_TABLES
WHERE SCHEMA_NAME = 'SAPHANADB'
  AND RECORD_COUNT > 100000
ORDER BY TABLE_SIZE DESC;

Largest column store tables by memory

SELECT TOP 50
       SCHEMA_NAME,
       TABLE_NAME,
       RECORD_COUNT,
       ROUND(MEMORY_SIZE_IN_TOTAL / 1024 / 1024 / 1024, 2) AS MEM_GB
FROM SYS.M_CS_TABLES
WHERE SCHEMA_NAME = 'SAPHANADB'
  AND RECORD_COUNT > 100000
ORDER BY MEMORY_SIZE_IN_TOTAL DESC;

Common archiving and cleanup suspects

SELECT TABLE_NAME, RECORD_COUNT,
       ROUND(TABLE_SIZE / 1024 / 1024 / 1024, 2) AS SIZE_GB
FROM SYS.M_TABLES
WHERE SCHEMA_NAME = 'SAPHANADB'
  AND TABLE_NAME IN ('CDHDR', 'CDPOS', 'EDIDC', 'EDID4', 'BALDAT', 'BALHDR', 'SWWLOGHIST', 'SWWWIHEAD', 'BKPF', 'BSEG', 'MKPF', 'MSEG')
ORDER BY TABLE_SIZE DESC;

Run as a database user with monitoring privileges, normally through HANA Studio, the HANA database explorer or hdbsql. Column and view names are standard in M_TABLES and M_CS_TABLES but verify on your HANA revision. In S/4HANA the finance and material document tables are different, so adjust the suspect list for the target. This query only reads monitoring views.

Review your data reduction plan

Share your top table list and retention constraints and we will comment on archiving priorities.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use the generated SQL

Enter your schema, how many tables you want and a minimum row count, then run the three statements in a HANA SQL console. The first query ranks tables by disk size from M_TABLES. The second ranks column store tables by memory from M_CS_TABLES, which is the number that drives HANA sizing and license cost. The third checks the tables that usually grow unchecked, such as change documents, IDocs, application logs and workflow logs.

Run on a recent copy of production
Save results by date to measure reduction
Compare memory totals with your sizing report

Reading the result

A handful of tables normally account for most of the database. Change document tables CDHDR and CDPOS, IDoc tables EDIDC and EDID4, application log tables BALHDR and BALDAT, and workflow tables are typical candidates for deletion or archiving under standard SAP archiving objects and reorganization reports. Large financial tables such as BKPF and BSEG are converted to the Universal Journal in S/4HANA, so archiving old fiscal years first reduces conversion volume and runtime.

Common mistakes

Do not delete directly from tables with SQL. Use the SAP archiving objects and deletion reports so that references stay consistent and audit rules are respected. Do not run the ranking only once, since cleanup changes the picture. Also confirm retention rules with finance and legal before archiving anything, because tax and audit requirements vary by country.

Frequently Asked Questions

What is the difference between M_TABLES and M_CS_TABLES?

M_TABLES covers all table types and reports disk size and row count. M_CS_TABLES covers column store tables and reports memory use, which is what drives HANA memory sizing.

Can I delete rows with SQL to reduce size?

You should not. Direct deletes bypass SAP consistency checks and are unsupported. Use the SAP archiving objects and standard deletion reports.

Why does the schema name matter?

SAP application tables live in one schema, often SAPHANADB or SAP followed by the system ID. Filtering by schema avoids listing system tables and tables from other applications.

How much can archiving save before conversion?

It depends on how old your data is and how long you kept logs. Systems with many years of unarchived change documents and IDocs can reduce volume noticeably, but measure with your own queries rather than assuming.

If you want a data reduction plan tied to your retention rules and conversion timeline, Netray can help review it.

Talk to an Expert Explore More Free Tools