Skip to content

Instantly share code, notes, and snippets.

@stevewithington

stevewithington/SAP-Tables-Columns-and-Descriptions.sql

Last active October 28, 2022 13:47
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save stevewithington/a527e4257635b0159a21fbb7f050cbf4 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save stevewithington/a527e4257635b0159a21fbb7f050cbf4 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. stevewithington revised this gist Oct 28, 2022. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions SAP-Tables-Columns-and-Descriptions.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -17,6 +17,7 @@ FROM {YOUR-SCHEMA}.DD02L D2L /* SAP Tables */
    INNER JOIN erp.DD02T D2T ON D2L.TABNAME = D2T.TABNAME /* SAP DD: SAP Table Texts */
    INNER JOIN erp.DD04T D4T ON D4T.ROLLNAME = D3L.ROLLNAME /* R/3 DD: Data Element Texts */
    WHERE 1=1
    --AND D2L.TABNAME = '{SOME-TABLE-NAME, e.g., LFA1}' /* To filter for specific table */
    AND D2L.AS4LOCAL = 'A' /* Activated Tables Only */
    AND D2L.TABCLASS IN ('TRANSP','POOL','CLUSTER') /* Ignoring: INITTAB (Structure), VIEW, and APPEND tables */
    AND D3L.PRECFIELD = ' ' /* Ignore .INCLUDE fields */
  2. stevewithington revised this gist Oct 28, 2022. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions SAP-Tables-Columns-and-Descriptions.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -7,10 +7,10 @@ SELECT
    , D2T.DDTEXT AS SOURCE_TABLE_DESC
    , D3L.FIELDNAME AS SOURCE_COLUMN_NAME
    , D4T.DDTEXT AS SOURCE_COLUMN_DESC
    , D4T.REPTEXT AS SOURCE_COLUMN_HEADER
    , D4T.SCRTEXT_S AS SOURCE_COLUMN_LABEL_SHORT
    , D4T.SCRTEXT_M AS SOURCE_COLUMN_LABEL_MEDIUM
    , D4T.SCRTEXT_L AS SOURCE_COLUMN_LABEL_LONG
    , D4T.REPTEXT AS SOURCE_COLUMN_HEADER
    , D4T.SCRTEXT_S AS SOURCE_COLUMN_LABEL_SHORT
    , D4T.SCRTEXT_M AS SOURCE_COLUMN_LABEL_MEDIUM
    , D4T.SCRTEXT_L AS SOURCE_COLUMN_LABEL_LONG
    FROM {YOUR-SCHEMA}.DD02L D2L /* SAP Tables */
    INNER JOIN erp.DD03L D3L ON D3L.TABNAME = D2L.TABNAME /* Table Fields */
    AND D2L.AS4LOCAL = D3L.AS4LOCAL /* Activation status */
  3. stevewithington revised this gist Oct 28, 2022. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions SAP-Tables-Columns-and-Descriptions.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -7,10 +7,10 @@ SELECT
    , D2T.DDTEXT AS SOURCE_TABLE_DESC
    , D3L.FIELDNAME AS SOURCE_COLUMN_NAME
    , D4T.DDTEXT AS SOURCE_COLUMN_DESC
    , D4.REPTEXT AS SOURCE_COLUMN_HEADER
    , D4.SCRTEXT_S AS SOURCE_COLUMN_LABEL_SHORT
    , D4.SCRTEXT_M AS SOURCE_COLUMN_LABEL_MEDIUM
    , D4.SCRTEXT_L AS SOURCE_COLUMN_LABEL_LONG
    , D4T.REPTEXT AS SOURCE_COLUMN_HEADER
    , D4T.SCRTEXT_S AS SOURCE_COLUMN_LABEL_SHORT
    , D4T.SCRTEXT_M AS SOURCE_COLUMN_LABEL_MEDIUM
    , D4T.SCRTEXT_L AS SOURCE_COLUMN_LABEL_LONG
    FROM {YOUR-SCHEMA}.DD02L D2L /* SAP Tables */
    INNER JOIN erp.DD03L D3L ON D3L.TABNAME = D2L.TABNAME /* Table Fields */
    AND D2L.AS4LOCAL = D3L.AS4LOCAL /* Activation status */
  4. stevewithington revised this gist Oct 28, 2022. 1 changed file with 4 additions and 0 deletions.
    4 changes: 4 additions & 0 deletions SAP-Tables-Columns-and-Descriptions.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -7,6 +7,10 @@ SELECT
    , D2T.DDTEXT AS SOURCE_TABLE_DESC
    , D3L.FIELDNAME AS SOURCE_COLUMN_NAME
    , D4T.DDTEXT AS SOURCE_COLUMN_DESC
    , D4.REPTEXT AS SOURCE_COLUMN_HEADER
    , D4.SCRTEXT_S AS SOURCE_COLUMN_LABEL_SHORT
    , D4.SCRTEXT_M AS SOURCE_COLUMN_LABEL_MEDIUM
    , D4.SCRTEXT_L AS SOURCE_COLUMN_LABEL_LONG
    FROM {YOUR-SCHEMA}.DD02L D2L /* SAP Tables */
    INNER JOIN erp.DD03L D3L ON D3L.TABNAME = D2L.TABNAME /* Table Fields */
    AND D2L.AS4LOCAL = D3L.AS4LOCAL /* Activation status */
  5. stevewithington renamed this gist Oct 28, 2022. 1 changed file with 0 additions and 0 deletions.
    0 SAP-TABLES-COLUMNS-AND-DESCRIPTIONS.sql → SAP-Tables-Columns-and-Descriptions.sql
    View file
    File renamed without changes.
  6. stevewithington renamed this gist Oct 28, 2022. 1 changed file with 0 additions and 0 deletions.
    0 SAP-TABLES-AND-COLUMNS-DESCRIPTIONS.sql → SAP-TABLES-COLUMNS-AND-DESCRIPTIONS.sql
    View file
    File renamed without changes.
  7. stevewithington renamed this gist Oct 28, 2022. 1 changed file with 0 additions and 0 deletions.
    0 SAP-TABLES-AND-FIELDS-DESCRIPTIONS.sql → SAP-TABLES-AND-COLUMNS-DESCRIPTIONS.sql
    View file
    File renamed without changes.
  8. stevewithington revised this gist Oct 28, 2022. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion SAP-TABLES-AND-FIELDS-DESCRIPTIONS.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -9,7 +9,7 @@ SELECT
    , D4T.DDTEXT AS SOURCE_COLUMN_DESC
    FROM {YOUR-SCHEMA}.DD02L D2L /* SAP Tables */
    INNER JOIN erp.DD03L D3L ON D3L.TABNAME = D2L.TABNAME /* Table Fields */
    AND D2L.AS4LOCAL = D3L.AS4LOCAL
    AND D2L.AS4LOCAL = D3L.AS4LOCAL /* Activation status */
    INNER JOIN erp.DD02T D2T ON D2L.TABNAME = D2T.TABNAME /* SAP DD: SAP Table Texts */
    INNER JOIN erp.DD04T D4T ON D4T.ROLLNAME = D3L.ROLLNAME /* R/3 DD: Data Element Texts */
    WHERE 1=1
  9. stevewithington revised this gist Oct 28, 2022. No changes.
  10. stevewithington created this gist Oct 28, 2022.
    21 changes: 21 additions & 0 deletions SAP-TABLES-AND-FIELDS-DESCRIPTIONS.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,21 @@
    USE {YOUR-SAP-DBNAME-GOES-HERE};
    GO

    SELECT
    D3L.POSITION AS SOURCE_COLUMN_POSITION
    , D2L.TABNAME AS SOURCE_TABLE_NAME
    , D2T.DDTEXT AS SOURCE_TABLE_DESC
    , D3L.FIELDNAME AS SOURCE_COLUMN_NAME
    , D4T.DDTEXT AS SOURCE_COLUMN_DESC
    FROM {YOUR-SCHEMA}.DD02L D2L /* SAP Tables */
    INNER JOIN erp.DD03L D3L ON D3L.TABNAME = D2L.TABNAME /* Table Fields */
    AND D2L.AS4LOCAL = D3L.AS4LOCAL
    INNER JOIN erp.DD02T D2T ON D2L.TABNAME = D2T.TABNAME /* SAP DD: SAP Table Texts */
    INNER JOIN erp.DD04T D4T ON D4T.ROLLNAME = D3L.ROLLNAME /* R/3 DD: Data Element Texts */
    WHERE 1=1
    AND D2L.AS4LOCAL = 'A' /* Activated Tables Only */
    AND D2L.TABCLASS IN ('TRANSP','POOL','CLUSTER') /* Ignoring: INITTAB (Structure), VIEW, and APPEND tables */
    AND D3L.PRECFIELD = ' ' /* Ignore .INCLUDE fields */
    AND D4T.DDLANGUAGE = 'E' /* English Only */
    AND D2T.DDLANGUAGE = 'E' /* English Only */
    ORDER BY D2T.TABNAME ASC, D3L.POSITION