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
Often SAPHANADB or SAP<SID>
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.
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.
Related Tools
SAP S/4HANA Conversion and Migration Tools Reference
A lookup of the SAP tools used at each phase of an S/4HANA conversion or migration, what each does and when to use it.
SAPSAP S/4HANA Pre-Conversion Technical Checklist
A checklist of the technical preparation items to close before running SUM on a system conversion from ECC to S/4HANA.
SAPSAP Custom Code Inventory ABAP Query Generator (TADIR)
Generates ABAP SELECT statements against TADIR that count and list custom objects by type and package for S/4HANA remediation planning.
SAPSAP S/4HANA Key Simplifications Reference
A searchable reference of the biggest ECC to S/4HANA data model and functional changes, with the action each one usually forces.