*&---------------------------------------------------------------------* *& Report Z_ERPFORGE_04_TABLE_INVENTORY *&---------------------------------------------------------------------* *& ERPForgeAI - Step 4: Table Inventory Export (DB-Volume-Audit) *& *& -- GOVERNANCE (read in under a minute) ------------------------------ *& READS : Per-table SIZE metadata via ADBC - row counts and storage, *& plus table names (DD02L). No table contents are read. *& DOES NOT : No UPDATE/INSERT/MODIFY/DELETE on DB tables (read-only). *& No RFC, no DESTINATION, no network/internet. No server-side *& file write. No business data / table rows / PII is read. *& Output is SAP METADATA only (sizes, not contents) - *& nothing leaves your system unless YOU export it. *& FOOTPRINT: Transient analysis report - delete after extraction. *& SOURCE : MIT-licensed; public source + SHA-256 checksum at *& https://erpforgeai.de/sap-tools.html *& --------------------------------------------------------------------- *& *& Reads Oracle DBA_SEGMENTS via SAP DBI/ADBC (no external DB access *& needed) and writes a semicolon-delimited CSV (Excel-DE default) *& compatible with the same parser used for `oracle_table_inventory.sql`. *& *& Use this when customer IT does NOT grant raw Oracle SELECT on *& DBA_SEGMENTS — this report runs as the SAP DB user, which already *& has the necessary read privileges. *& *& VERSION: v0.5 (size column is now KB instead of BYTES — divide by *& 1024 + ROUND inside SQL so values arrive as integer *& kilobytes; column renamed BYTES → KB throughout). *& Workflow: run on a real customer ECC/BW system on Oracle, send the *& CSV output to ERPforgeAI, parser is built from real output. *& *& Compatible: SAP_BASIS 7.40 SP02+, Oracle 11g+, SAP-on-Oracle. *& *& Output schema (semicolon-delimited, RFC 4180 quoting): *& TABLE_NAME ; KB ; NUM_ROWS ; TABCLASS ; IS_CUSTOM *& ; DEVCLASS ; DESCRIPTION ; LAST_ANALYZED *& *& PII: no user data. Last-analyzed is a system timestamp. *& *& MEMORY-SAFE: cap at 1000 rows (top-N by size is enough for *& HANA-sizing decisions; the long tail is individually negligible). *&---------------------------------------------------------------------* REPORT z_erpforge_04_table_inventory. CONSTANTS: gc_top_n TYPE i VALUE 1000. CONSTANTS: gc_tab TYPE c VALUE cl_abap_char_utilities=>horizontal_tab. TYPES: BEGIN OF ty_seg, table_name TYPE c LENGTH 30, kb TYPE p LENGTH 16 DECIMALS 0, num_rows TYPE p LENGTH 16 DECIMALS 0, tabclass TYPE c LENGTH 8, is_custom TYPE c LENGTH 1, devclass TYPE tadir-devclass, description TYPE c LENGTH 60, last_analyzed TYPE c LENGTH 10, END OF ty_seg, tt_seg TYPE TABLE OF ty_seg. DATA: gt_out TYPE tt_seg. " ─── Self-contained labels ──────────────────────────────────────────────── " The SELECTION-SCREEN ... TITLE and COMMENT n(len) statements " IMPLICITLY declare as a character data object of the given length. " Do NOT add an explicit DATA: gv_title / gv_comm1 / gv_comm2 — that would " double-declare them ("ist bereits deklariert"). And do NOT use TEXT-xxx " symbols here — those are read-only text-pool entries that cannot be " modified in INITIALIZATION ("Das Feld TEXT-xxx darf nicht verändert " werden"). The auto-declared variables ARE assignable at runtime, which is " exactly what we need for self-contained labels. SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE gv_title. SELECTION-SCREEN COMMENT 1(70) gv_comm1. PARAMETERS: p_max_n TYPE i DEFAULT 1000. SELECTION-SCREEN SKIP 1. SELECTION-SCREEN COMMENT 1(70) gv_comm2. PARAMETERS: p_file TYPE string LOWER CASE DEFAULT 'C:\Temp\Z_ERPFORGE_04_TABLE_INVENTORY.csv'. SELECTION-SCREEN END OF BLOCK b1. INITIALIZATION. " Block frame title and comment lines (auto-declared C-fields, assignable). gv_title = 'Tabellen-Inventar exportieren'. gv_comm1 = 'Anzahl der zu exportierenden groessten Tabellen (Top-N).'. gv_comm2 = 'Pfad auf dem Frontend-PC, wohin die CSV-Datei geschrieben wird.'. " Labels NEXT TO the input fields (selection-text equivalent). " The %__%_app_%-text fields ARE assignable at runtime — they are " SAP-internal screen fields, NOT text-pool symbols. %_p_max_n_%_app_%-text = 'Anzahl Tabellen (max.)'. %_p_file_%_app_%-text = 'Ausgabe-Datei'. START-OF-SELECTION. WRITE: / 'Z_ERPFORGE_04_TABLE_INVENTORY v0.5'. WRITE: / '====================================='. " Native SQL via ADBC. We query Oracle DBA_SEGMENTS joined to DBA_TABLES " for row counts, plus SAP DD02L for table-class and TADIR for devclass. " Size is converted from bytes to kilobytes inside SQL (ROUND of bytes/1024) " so the value arrives in ABAP already as an integer KB number. TRY. DATA(lo_sql) = NEW cl_sql_statement( ). DATA(lv_sql) = |SELECT seg.segment_name, ROUND(SUM(seg.bytes) / 1024) AS kb,| && | NVL(MAX(tab.num_rows), 0) AS num_rows,| && | NVL(MAX(dd.tabclass), '?') AS tabclass,| && | CASE WHEN REGEXP_LIKE(seg.segment_name, '^[ZY]')| && | OR seg.segment_name LIKE '/%' THEN 'X' ELSE '' END AS is_custom,| && | NVL(MAX(td.devclass), '') AS devclass,| && | NVL(MAX(CASE WHEN ddt.ddlanguage = 'D' THEN ddt.ddtext END),| && | NVL(MAX(CASE WHEN ddt.ddlanguage = 'E' THEN ddt.ddtext END),| && | MAX(ddt.ddtext))) AS description,| && | TO_CHAR(MAX(tab.last_analyzed), 'YYYY-MM-DD') AS last_analyzed| && | FROM dba_segments seg| && | LEFT JOIN dba_tables tab ON tab.owner = seg.owner AND tab.table_name = seg.segment_name| && | LEFT JOIN dd02l dd ON dd.tabname = seg.segment_name| && | LEFT JOIN dd02t ddt ON ddt.tabname = seg.segment_name| && | LEFT JOIN tadir td ON td.pgmid = 'R3TR' AND td.object = 'TABL'| && | AND td.obj_name = seg.segment_name| && | WHERE seg.segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')| && | AND seg.owner = (SELECT MAX(owner) FROM dba_tables WHERE table_name = 'DD02L')| && | GROUP BY seg.segment_name| && | ORDER BY SUM(seg.bytes) DESC| && | FETCH FIRST { p_max_n } ROWS ONLY|. DATA(lo_result) = lo_sql->execute_query( lv_sql ). lo_result->set_param_table( REF #( gt_out ) ). lo_result->next_package( ). lo_result->close( ). CATCH cx_sql_exception INTO DATA(lx_sql). WRITE: / 'SQL error:', lx_sql->get_text( ). RETURN. ENDTRY. WRITE: / |Rows fetched: { lines( gt_out ) }|. " Write semicolon-delimited CSV (DACH Excel default — Excel-DE opens " ;-CSVs natively without the "Text-to-Columns" wizard). Fields that " contain ; or " or newlines are wrapped in double quotes per RFC 4180. DATA: lt_lines TYPE TABLE OF string, lv_line TYPE string, lv_desc TYPE string. CONSTANTS: gc_sep TYPE c VALUE ';'. " UTF-8 BOM at file start so Excel-DE auto-detects encoding for umlauts lv_line = cl_abap_char_utilities=>byte_order_mark_utf8 && |TABLE_NAME;KB;NUM_ROWS;TABCLASS;IS_CUSTOM;DEVCLASS;DESCRIPTION;LAST_ANALYZED|. APPEND lv_line TO lt_lines. LOOP AT gt_out ASSIGNING FIELD-SYMBOL(). " Quote description if it contains the separator or a quote lv_desc = -description. IF lv_desc CA ';"' OR lv_desc CA cl_abap_char_utilities=>cr_lf. REPLACE ALL OCCURRENCES OF '"' IN lv_desc WITH '""'. lv_desc = |"{ lv_desc }"|. ENDIF. lv_line = |{ -table_name };{ -kb NUMBER = USER };| && |{ -num_rows NUMBER = USER };{ -tabclass };| && |{ -is_custom };{ -devclass };| && |{ lv_desc };{ -last_analyzed }|. APPEND lv_line TO lt_lines. ENDLOOP. cl_gui_frontend_services=>gui_download( EXPORTING filename = p_file filetype = 'ASC' write_field_separator = ' ' trunc_trailing_blanks = abap_true CHANGING data_tab = lt_lines EXCEPTIONS OTHERS = 1 ). IF sy-subrc <> 0. WRITE: / 'gui_download failed:', sy-subrc. ELSE. WRITE: / 'Inventory written to:', p_file. WRITE: / 'Upload to https://erpforgeai.de/app/dbvol-check'. ENDIF.