SAP Table for Table Fields, Table Descriptions and Error Message Text: Query Generator

REPORT z_ddic_and_message_lookup.

* Mode FIELDS : fields of a table with data element and long label
* Mode FIND   : transparent tables whose short text contains a word
* Mode MESSAGE: long text of a message class and number from T100

TYPES: BEGIN OF ty_field,
         position  TYPE dd03l-position,
         fieldname TYPE dd03l-fieldname,
         keyflag   TYPE dd03l-keyflag,
         rollname  TYPE dd03l-rollname,
         datatype  TYPE dd03l-datatype,
         leng      TYPE dd03l-leng,
         scrtext_l TYPE dd04t-scrtext_l,
       END OF ty_field,
       BEGIN OF ty_tab,
         tabname TYPE dd02t-tabname,
         ddtext  TYPE dd02t-ddtext,
       END OF ty_tab.

DATA: lt_fields  TYPE STANDARD TABLE OF ty_field,
      ls_field   TYPE ty_field,
      lt_tabs    TYPE STANDARD TABLE OF ty_tab,
      ls_tab     TYPE ty_tab,
      lv_msgtext TYPE t100-text,
      lv_pattern TYPE c LENGTH 64.

START-OF-SELECTION.

  CASE 'FIELDS'.

    WHEN 'FIELDS'.
* .INCLUDE rows appear with FIELDNAME '.INCLUDE' and no data element
      SELECT d3~position d3~fieldname d3~keyflag d3~rollname
             d3~datatype d3~leng d4~scrtext_l
        FROM dd03l AS d3
        LEFT OUTER JOIN dd04t AS d4
          ON  d4~rollname   = d3~rollname
          AND d4~ddlanguage = 'E'
          AND d4~as4local   = 'A'
          AND d4~as4vers    = '0000'
        INTO TABLE lt_fields
        UP TO 500 ROWS
        WHERE d3~tabname  = 'MARA'
          AND d3~as4local = 'A'
          AND d3~as4vers  = '0000'
        ORDER BY d3~position.
      LOOP AT lt_fields INTO ls_field.
        WRITE: / ls_field-position, ls_field-fieldname, ls_field-keyflag,
                 ls_field-rollname, ls_field-datatype, ls_field-leng,
                 ls_field-scrtext_l.
      ENDLOOP.

    WHEN 'FIND'.
* DDTEXT is mixed case; LIKE is case-sensitive on most databases
      CONCATENATE '%' 'Vendor' '%' INTO lv_pattern.
      SELECT t~tabname t~ddtext
        FROM dd02t AS t
        INNER JOIN dd02l AS l
          ON  l~tabname  = t~tabname
          AND l~as4local = t~as4local
          AND l~as4vers  = t~as4vers
        INTO TABLE lt_tabs
        UP TO 500 ROWS
        WHERE t~ddlanguage = 'E'
          AND t~as4local   = 'A'
          AND t~ddtext     LIKE lv_pattern
          AND l~tabclass   = 'TRANSP'.
      SORT lt_tabs BY tabname.
      LOOP AT lt_tabs INTO ls_tab.
        WRITE: / ls_tab-tabname, ls_tab-ddtext.
      ENDLOOP.

    WHEN 'MESSAGE'.
* &1..&4 (or &) in the text are filled from MSGV1..MSGV4 at runtime
      SELECT SINGLE text FROM t100 INTO lv_msgtext
        WHERE sprsl = 'E'
          AND arbgb = 'M7'
          AND msgnr = '021'.
      IF sy-subrc = 0.
        WRITE: / 'M7', '021', lv_msgtext.
      ELSE.
        WRITE: / 'Message not found in language', 'E'.
      ENDIF.

  ENDCASE.

* Native SQL equivalents for DBA tools (read-only). The schema owner
* varies by installation (often SAPSR3 on ECC, SAP<SID> or SAPABAP1):
* SELECT POSITION, FIELDNAME, KEYFLAG, ROLLNAME, DATATYPE, LENG
*   FROM SAPSR3.DD03L
*  WHERE TABNAME = 'MARA' AND AS4LOCAL = 'A' ORDER BY POSITION;
* SELECT TABNAME, DDTEXT FROM SAPSR3.DD02T
*  WHERE DDLANGUAGE = 'E' AND DDTEXT LIKE '%Vendor%';
* SELECT TEXT FROM SAPSR3.T100
*  WHERE SPRSL = 'E' AND ARBGB = 'M7' AND MSGNR = '021';