Simple, Fast and Useful SAP Search Engine

Most of the times users want to search any transaction or table by description or short text. But the problem with description or short text search is that it is case sensitive search and the user doesn’t know which letters are in uppercase and which letters are in lower case. The reason for this is that in the domain of the short text or description there is a flag called lowercase which is checked. SAP search helps needs the key words in sequence seperated by wild characters(*). e.g. If we give Job Overview or Overview Job we should be able to get transaction SM37.

I created a program which is simple, fast and very useful search for Transactions, Database tables, Functions and Programs. I have used native SQL, which is very fast and directly communicates with the underlying database without Database interface. This program works on all databases supported by SAP. It works with 46C, 47 and later releases of SAP. The search is case insensitive, no sequence of key words required, no wild characters required. The only limitation of this program is that it supports maximum of 10 key words for search based on short text or description.

Below is the ABAP code for the search program:

*************************************************************WRITTEN BY BHASKAR MADUGULA 9 NOV 2004*SAP Hot Search Engine for Transactions, Tables, Programs, *Functions*************************************************************TEXT SYMBOLS*************************************************************001 *  Enter search terms seperated by SPACE only*002 *  NO CASE SENSITVE, NO SEQUENCE REQUIRED, *          NO WILD CHARACTERS REQUIRED************************************************************************SELECTION TEXTS*************************************************************DDTEXT    Table Text*FTEXT     Function Text*FUNC     Search for Functions*FUNCTION  Function Module*PROGM     Search for Programs*PROGRAM   Program Name*REPTI     Program Text*TABLE     Search for Database Tables*TABNAME   Table Name*TCODE     Transaction Code*TRANS     Search for Transactions*TTEXT     Transaction Text************************************************************REPORT Z_HOT_SEARCH NO STANDARD PAGE HEADING LINE-SIZE 120.DATA : TEXT(256) TYPE C,       TEXT1(256) TYPE C,       UCOMM TYPE SY-UCOMM.DATA : F1 TYPE TEXT VALUE ‘%’,       F2 TYPE TEXT VALUE ‘%’,       F3 TYPE TEXT VALUE ‘%’,       F4 TYPE TEXT VALUE ‘%’,       F5 TYPE TEXT VALUE ‘%’,       F6 TYPE TEXT VALUE ‘%’,       F7 TYPE TEXT VALUE ‘%’,       F8 TYPE TEXT VALUE ‘%’,       F9 TYPE TEXT VALUE ‘%’,       F10 TYPE TEXT VALUE ‘%’.DATA : FIELD1(256) TYPE C,       FIELD2(256) TYPE C,       FIELD3(256) TYPE C.DATA : MAINTEXT1 TYPE TEXT,       MAINTEXT2 TYPE TEXT,       MAINTEXT_TEMP TYPE TEXT.DATA : FLAG_COL TYPE I VALUE 1.DATA :  G_T_TEXT_TABLE TYPE TABLE OF TEXT WITH HEADER LINE.SELECTION-SCREEN BEGIN OF BLOCK PART WITH FRAME.PARAMETERS : TRANS RADIOBUTTON GROUP GRP,             TABLE RADIOBUTTON GROUP GRP,             PROGM RADIOBUTTON GROUP GRP,             FUNC RADIOBUTTON GROUP GRP.SELECTION-SCREEN END OF BLOCK PART.SELECTION-SCREEN BEGIN OF BLOCK PAR WITH FRAME.SELECTION-SCREEN BEGIN OF LINE.SELECTION-SCREEN COMMENT 1(79) TEXT-001.SELECTION-SCREEN END OF LINE.SELECTION-SCREEN BEGIN OF LINE.SELECTION-SCREEN COMMENT 1(79) TEXT-002.SELECTION-SCREEN END OF LINE.SELECTION-SCREEN END OF BLOCK PAR.SELECTION-SCREEN BEGIN OF SCREEN 120.SELECTION-SCREEN BEGIN OF BLOCK PART1 WITH FRAME.PARAMETERS : TTEXT TYPE TTEXT_STCT,             TCODE TYPE TCODE.SELECTION-SCREEN END OF BLOCK PART1.SELECTION-SCREEN END   OF SCREEN 120.SELECTION-SCREEN BEGIN OF SCREEN 121.SELECTION-SCREEN BEGIN OF BLOCK PART2 WITH FRAME.PARAMETERS : DDTEXT TYPE AS4TEXT,             TABNAME TYPE TABNAME.SELECTION-SCREEN END OF BLOCK PART2.SELECTION-SCREEN END   OF SCREEN 121.SELECTION-SCREEN BEGIN OF SCREEN 122.SELECTION-SCREEN BEGIN OF BLOCK PART3 WITH FRAME.PARAMETERS : REPTI TYPE REPTI,             PROGRAM TYPE PROGRAMM.SELECTION-SCREEN END OF BLOCK PART3.SELECTION-SCREEN END   OF SCREEN 122.SELECTION-SCREEN BEGIN OF SCREEN 123.SELECTION-SCREEN BEGIN OF BLOCK PART4 WITH FRAME.PARAMETERS : FTEXT TYPE RS38L_FTXT,             FUNCTION TYPE RS38L_FNAM.SELECTION-SCREEN END OF BLOCK PART4.SELECTION-SCREEN END   OF SCREEN 123.TOP-OF-PAGE.*WRITE : / ‘Welcome’, sy-uname.IF NOT TRANS IS INITIAL.  WRITE : / SY-ULINE.  WRITE AT /1 SY-VLINE.  FORMAT COLOR COL_HEADING.  WRITE AT 2 ‘TRANSACTION CODE’.  WRITE AT 24 SY-VLINE.  WRITE AT 25 ‘TRANSACTION TEXT’.  WRITE AT 69 SY-VLINE.  WRITE AT 70 ‘PROGRAM NAME’.  WRITE AT 120 SY-VLINE.  WRITE : / SY-ULINE.  FORMAT COLOR OFF.ELSEIF NOT TABLE IS INITIAL.  WRITE : / SY-ULINE.  WRITE AT /1 SY-VLINE.  FORMAT COLOR COL_HEADING.  WRITE AT 2 ‘TABLE’.  WRITE AT 39 SY-VLINE.  WRITE AT 40 ‘TEXT’.  WRITE AT 100 SY-VLINE.  WRITE AT 101 ‘TABLE CLASS’.  WRITE AT 120 SY-VLINE.  WRITE : / SY-ULINE.  FORMAT COLOR OFF.ELSEIF NOT PROGM IS INITIAL.  WRITE : / SY-ULINE.  WRITE AT /1 SY-VLINE.  FORMAT COLOR COL_HEADING.  WRITE AT 2 ‘PROGRAM’.  WRITE AT 39 SY-VLINE.  WRITE AT 40 ‘TEXT’.  WRITE AT 120 SY-VLINE.  WRITE : / SY-ULINE.  FORMAT COLOR OFF.ELSEIF NOT FUNC IS INITIAL.  WRITE : / SY-ULINE.  WRITE AT /1 SY-VLINE.  FORMAT COLOR COL_HEADING.  WRITE AT 2 ‘FUNCTION’.  WRITE AT 39 SY-VLINE.  WRITE AT 40 ‘TEXT’.  WRITE AT 120 SY-VLINE.  WRITE : / SY-ULINE.  FORMAT COLOR OFF.ENDIF.START-OF-SELECTION.IF NOT TRANS IS INITIAL. CALL SELECTION-SCREEN 120 STARTING AT 15 5 ENDING AT 100 10.ELSEIF NOT TABLE IS INITIAL. CALL SELECTION-SCREEN 121 STARTING AT 15 5 ENDING AT 100 10.ELSEIF NOT PROGM IS INITIAL. CALL SELECTION-SCREEN 122 STARTING AT 15 5 ENDING AT 100 10.ELSEIF NOT FUNC IS INITIAL. CALL SELECTION-SCREEN 123 STARTING AT 15 5 ENDING AT 100 10.ENDIF.AT SELECTION-SCREEN ON BLOCK PART1. IF TCODE IS INITIAL AND     TTEXT IS INITIAL.    MESSAGE E058(00) with ‘Transaction Code/Text’. ENDIF.AT SELECTION-SCREEN ON BLOCK PART2. IF DDTEXT IS INITIAL AND   TABNAME IS INITIAL AND   UCOMM IS INITIAL.  MESSAGE E058(00) with ‘Table/Text’. ENDIF.AT SELECTION-SCREEN ON BLOCK PART3. IF REPTI IS INITIAL AND   PROGRAM IS INITIAL AND   UCOMM IS INITIAL.  MESSAGE E058(00) with ‘Program/Text’. ENDIF.START-OF-SELECTION.IF NOT TTEXT IS INITIAL.   MAINTEXT1 = TTEXT.ENDIF.IF NOT TCODE IS INITIAL.   MAINTEXT2 = TCODE.ENDIF.IF NOT DDTEXT IS INITIAL.   MAINTEXT1 = DDTEXT.ENDIF.IF NOT TABNAME IS INITIAL.   MAINTEXT2 = TABNAME.ENDIF.IF NOT REPTI IS INITIAL.   MAINTEXT1 = REPTI.ENDIF.IF NOT PROGRAM IS INITIAL.   MAINTEXT2 = PROGRAM.ENDIF.IF NOT FTEXT IS INITIAL.   MAINTEXT1 = FTEXT.ENDIF.IF NOT FUNCTION IS INITIAL.   MAINTEXT2 = FUNCTION.ENDIF.IF MAINTEXT1 IS INITIAL AND   MAINTEXT2 IS INITIAL.   EXIT.ENDIF.IF NOT MAINTEXT2 IS INITIAL.  CONDENSE MAINTEXT2.  TRANSLATE MAINTEXT2 USING ‘* ‘.  TRANSLATE MAINTEXT2 USING ‘% ‘.  CONDENSE MAINTEXT2.  CONCATENATE ‘%’ MAINTEXT2 INTO MAINTEXT2.  CONCATENATE MAINTEXT2 ‘%+’ INTO MAINTEXT2.  TRANSLATE MAINTEXT2 USING ‘ %’.  SPLIT MAINTEXT2 AT ‘+’ INTO MAINTEXT2 MAINTEXT_TEMP.ENDIF.IF MAINTEXT2 IS INITIAL.  MAINTEXT2 = ‘%’.ENDIF.CONDENSE MAINTEXT1.TRANSLATE MAINTEXT1 USING ‘* ‘.TRANSLATE MAINTEXT1 USING ‘% ‘.CONDENSE MAINTEXT1.IF NOT MAINTEXT1 IS INITIAL.  SPLIT MAINTEXT1 AT ‘ ‘ INTO TABLE G_T_TEXT_TABLE.ENDIF.SORT G_T_TEXT_TABLE.DELETE ADJACENT DUPLICATES FROM G_T_TEXT_TABLE.READ TABLE G_T_TEXT_TABLE INDEX 1.IF SY-SUBRC IS INITIAL. TRANSLATE G_T_TEXT_TABLE TO UPPER CASE. CONCATENATE ‘%’ G_T_TEXT_TABLE ‘%’ INTO TEXT1. F1 = TEXT1.ENDIF.READ TABLE G_T_TEXT_TABLE INDEX 2.IF SY-SUBRC IS INITIAL. TRANSLATE G_T_TEXT_TABLE TO UPPER CASE. CONCATENATE ‘%’ G_T_TEXT_TABLE ‘%’ INTO TEXT1. F2 = TEXT1.ENDIF.READ TABLE G_T_TEXT_TABLE INDEX 3.IF SY-SUBRC IS INITIAL. TRANSLATE G_T_TEXT_TABLE TO UPPER CASE. CONCATENATE ‘%’ G_T_TEXT_TABLE ‘%’ INTO TEXT1. F3 = TEXT1.ENDIF.READ TABLE G_T_TEXT_TABLE INDEX 4.IF SY-SUBRC IS INITIAL. TRANSLATE G_T_TEXT_TABLE TO UPPER CASE. CONCATENATE ‘%’ G_T_TEXT_TABLE ‘%’ INTO TEXT1. F4 = TEXT1.ENDIF.READ TABLE G_T_TEXT_TABLE INDEX 5.IF SY-SUBRC IS INITIAL. TRANSLATE G_T_TEXT_TABLE TO UPPER CASE. CONCATENATE ‘%’ G_T_TEXT_TABLE ‘%’ INTO TEXT1. F5 = TEXT1.ENDIF.READ TABLE G_T_TEXT_TABLE INDEX 6.IF SY-SUBRC IS INITIAL. TRANSLATE G_T_TEXT_TABLE TO UPPER CASE. CONCATENATE ‘%’ G_T_TEXT_TABLE ‘%’ INTO TEXT1. F6 = TEXT1.ENDIF.READ TABLE G_T_TEXT_TABLE INDEX 7.IF SY-SUBRC IS INITIAL. TRANSLATE G_T_TEXT_TABLE TO UPPER CASE. CONCATENATE ‘%’ G_T_TEXT_TABLE ‘%’ INTO TEXT1. F7 = TEXT1.ENDIF.READ TABLE G_T_TEXT_TABLE INDEX 8.IF SY-SUBRC IS INITIAL. TRANSLATE G_T_TEXT_TABLE TO UPPER CASE. CONCATENATE ‘%’ G_T_TEXT_TABLE ‘%’ INTO TEXT1. F8 = TEXT1.ENDIF.READ TABLE G_T_TEXT_TABLE INDEX 9.IF SY-SUBRC IS INITIAL. TRANSLATE G_T_TEXT_TABLE TO UPPER CASE. CONCATENATE ‘%’ G_T_TEXT_TABLE ‘%’ INTO TEXT1. F9 = TEXT1.ENDIF.READ TABLE G_T_TEXT_TABLE INDEX 10.IF SY-SUBRC IS INITIAL. TRANSLATE G_T_TEXT_TABLE TO UPPER CASE. CONCATENATE ‘%’ G_T_TEXT_TABLE ‘%’ INTO TEXT1. F10 = TEXT1.ENDIF.IF NOT TRANS IS INITIAL.  EXEC SQL PERFORMING loop_output.    SELECT TCODE, TTEXT, PGMNA INTO :FIELD1, :FIELD2, :FIELD3 FROM TSTCV           WHERE SPRSL = :’E’ AND                 TCODE LIKE :MAINTEXT2 AND                 UPPER( TTEXT ) LIKE :F1 AND                 UPPER( TTEXT ) LIKE :F2 AND                 UPPER( TTEXT ) LIKE :F3 AND                 UPPER( TTEXT ) LIKE :F4 AND                 UPPER( TTEXT ) LIKE :F5 AND                 UPPER( TTEXT ) LIKE :F6 AND                 UPPER( TTEXT ) LIKE :F7 AND                 UPPER( TTEXT ) LIKE :F8 AND                 UPPER( TTEXT ) LIKE :F9 AND                 UPPER( TTEXT ) LIKE :F10  ENDEXEC.  IF NOT SY-SUBRC IS INITIAL AND SY-DBCNT IS INITIAL.    WRITE : AT /1 SY-VLINE.    WRITE : AT 40 ‘No table entries found for specified selection’            COLOR COL_NEGATIVE,            AT 120 SY-VLINE.  ENDIF.ELSEIF NOT TABLE IS INITIAL.  EXEC SQL PERFORMING loop_output1.    SELECT TABNAME, DDTEXT, TABCLASS INTO :FIELD1, :FIELD2, :FIELD3           FROM DD02V           WHERE DDLANGUAGE = :’E’ AND                 TABNAME LIKE :MAINTEXT2 AND                 TABCLASS IN (:’TRANSP’, ‘CLUSTER’, ‘POOL’, ‘VIEW’) AND                 UPPER( DDTEXT ) LIKE :F1 AND                 UPPER( DDTEXT ) LIKE :F2 AND                 UPPER( DDTEXT ) LIKE :F3 AND                 UPPER( DDTEXT ) LIKE :F4 AND                 UPPER( DDTEXT ) LIKE :F5 AND                 UPPER( DDTEXT ) LIKE :F6 AND                 UPPER( DDTEXT ) LIKE :F7 AND                 UPPER( DDTEXT ) LIKE :F8 AND                 UPPER( DDTEXT ) LIKE :F9 AND                 UPPER( DDTEXT ) LIKE :F10  ENDEXEC.*  EXEC SQL PERFORMING loop_output1.*    SELECT TABNAME, DDTEXT INTO :FIELD1, :FIELD2 FROM DD02T*           WHERE DDLANGUAGE = :’E’ AND*                 TABNAME LIKE :MAINTEXT2 AND*                 UPPER( DDTEXT ) LIKE :F1 AND*                 UPPER( DDTEXT ) LIKE :F2 AND*                 UPPER( DDTEXT ) LIKE :F3 AND*                 UPPER( DDTEXT ) LIKE :F4 AND*                 UPPER( DDTEXT ) LIKE :F5 AND*                 UPPER( DDTEXT ) LIKE :F6 AND*                 UPPER( DDTEXT ) LIKE :F7 AND*                 UPPER( DDTEXT ) LIKE :F8 AND*                 UPPER( DDTEXT ) LIKE :F9 AND*                 UPPER( DDTEXT ) LIKE :F10*  ENDEXEC.  IF NOT SY-SUBRC IS INITIAL AND SY-DBCNT IS INITIAL.    WRITE : AT /1 SY-VLINE.    WRITE : AT 40 ‘No table entries found for specified selection’            COLOR COL_NEGATIVE,            AT 120 SY-VLINE.  ENDIF.ELSEIF NOT PROGM IS INITIAL.  EXEC SQL PERFORMING loop_output2.    SELECT NAME, TEXT INTO :FIELD1, :FIELD2 FROM TRDIRT           WHERE SPRSL = :’E’ AND                 NAME LIKE :MAINTEXT2 AND                 UPPER( TEXT ) LIKE :F1 AND                 UPPER( TEXT ) LIKE :F2 AND                 UPPER( TEXT ) LIKE :F3 AND                 UPPER( TEXT ) LIKE :F4 AND                 UPPER( TEXT ) LIKE :F5 AND                 UPPER( TEXT ) LIKE :F6 AND                 UPPER( TEXT ) LIKE :F7 AND                 UPPER( TEXT ) LIKE :F8 AND                 UPPER( TEXT ) LIKE :F9 AND                 UPPER( TEXT ) LIKE :F10  ENDEXEC.  IF NOT SY-SUBRC IS INITIAL AND SY-DBCNT IS INITIAL.    WRITE : AT /1 SY-VLINE.    WRITE : AT 40 ‘No table entries found for specified selection’            COLOR COL_NEGATIVE,            AT 120 SY-VLINE.  ENDIF.ELSEIF NOT FUNC IS INITIAL.  EXEC SQL PERFORMING loop_output3.    SELECT FUNCNAME, STEXT INTO :FIELD1, :FIELD2 FROM TFTIT           WHERE SPRAS = :’E’ AND                 FUNCNAME LIKE :MAINTEXT2 AND                 UPPER( STEXT ) LIKE :F1 AND                 UPPER( STEXT ) LIKE :F2 AND                 UPPER( STEXT ) LIKE :F3 AND                 UPPER( STEXT ) LIKE :F4 AND                 UPPER( STEXT ) LIKE :F5 AND                 UPPER( STEXT ) LIKE :F6 AND                 UPPER( STEXT ) LIKE :F7 AND                 UPPER( STEXT ) LIKE :F8 AND                 UPPER( STEXT ) LIKE :F9 AND                 UPPER( STEXT ) LIKE :F10  ENDEXEC.  IF NOT SY-SUBRC IS INITIAL AND SY-DBCNT IS INITIAL.    WRITE : AT /1 SY-VLINE.    WRITE : AT 40 ‘No table entries found for specified selection’            COLOR COL_NEGATIVE,            AT 120 SY-VLINE.  ENDIF.ENDIF.WRITE : / SY-ULINE HOTSPOT OFF.FORM LOOP_OUTPUT.        IF FLAG_COL = 1.          FORMAT COLOR COL_NORMAL INTENSIFIED ON.          FLAG_COL = 2.        ELSEIF FLAG_COL = 2.          FORMAT COLOR COL_NORMAL INTENSIFIED OFF.          FLAG_COL = 1.        ENDIF.        WRITE : / FIELD1 UNDER ‘TRANSACTION CODE’ HOTSPOT ON,                  AT 1 SY-VLINE, AT 24 SY-VLINE HOTSPOT OFF,                  FIELD2 UNDER ‘TRANSACTION TEXT’ HOTSPOT ON,                  AT 69 SY-VLINE HOTSPOT OFF,                  FIELD3 UNDER ‘PROGRAM NAME’ HOTSPOT ON,                  AT 120 SY-VLINE HOTSPOT OFF.       FORMAT COLOR OFF.ENDFORM.FORM LOOP_OUTPUT1.        IF FLAG_COL = 1.          FORMAT COLOR COL_NORMAL INTENSIFIED ON.          FLAG_COL = 2.        ELSEIF FLAG_COL = 2.          FORMAT COLOR COL_NORMAL INTENSIFIED OFF.          FLAG_COL = 1.        ENDIF.        FORMAT COLOR COL_NORMAL.        WRITE : / FIELD1 UNDER ‘TABLE’ HOTSPOT ON,                 AT 1 SY-VLINE, AT 39 SY-VLINE,                 FIELD2 UNDER ‘TEXT’ HOTSPOT ON, AT 100 SY-VLINE,                 FIELD3 UNDER ‘TABLE CLASS’ HOTSPOT ON, AT 120 SY-VLINE.       FORMAT COLOR OFF.ENDFORM.FORM LOOP_OUTPUT2.        IF FLAG_COL = 1.          FORMAT COLOR COL_NORMAL INTENSIFIED ON.          FLAG_COL = 2.        ELSEIF FLAG_COL = 2.          FORMAT COLOR COL_NORMAL INTENSIFIED OFF.          FLAG_COL = 1.        ENDIF.        FORMAT COLOR COL_NORMAL.        WRITE : / FIELD1 UNDER ‘PROGRAM’ HOTSPOT ON,                  AT 1 SY-VLINE, AT 39 SY-VLINE,                  FIELD2 UNDER ‘TEXT’ HOTSPOT ON, AT 120 SY-VLINE.       FORMAT COLOR OFF.ENDFORM.FORM LOOP_OUTPUT3.        IF FLAG_COL = 1.          FORMAT COLOR COL_NORMAL INTENSIFIED ON.          FLAG_COL = 2.        ELSEIF FLAG_COL = 2.          FORMAT COLOR COL_NORMAL INTENSIFIED OFF.          FLAG_COL = 1.        ENDIF.        FORMAT COLOR COL_NORMAL.        WRITE : / FIELD1 UNDER ‘FUNCTION’ HOTSPOT ON,                  AT 1 SY-VLINE, AT 39 SY-VLINE,                  FIELD2 UNDER ‘TEXT’ HOTSPOT ON, AT 120 SY-VLINE.       FORMAT COLOR OFF.ENDFORM.AT LINE-SELECTION. IF SY-LILLI < 4 OR    SY-LISEL+1(20) = ‘——————–‘.     MESSAGE E301(00).     EXIT. ENDIF.*Transaction IF NOT TRANS IS INITIAL.    CALL TRANSACTION SY-LISEL+1(20).*Table ELSEIF NOT TABLE IS INITIAL.    SET PARAMETER ID ‘DTB’ FIELD SY-LISEL+1(30) .    CALL TRANSACTION ‘SE16’.*Program ELSEIF NOT PROGM IS INITIAL.    SET PARAMETER ID ‘RID’ FIELD SY-LISEL+1(30) .    CALL TRANSACTION ‘SE38’. ELSEIF NOT FUNC IS INITIAL.*Function  SET PARAMETER ID ‘LIB’ FIELD SY-LISEL+1(30) .  CALL TRANSACTION ‘SE37’. ENDIF.