{UTILITY | Interactive: Find all Tables containing a given column Name }
--rbse: generic
--file: 0findColumn.rmd
--auth: bachitiea
--crea: 2020-08-12
--last: 2024-12-04
--vers: v.2.0

------------------------------------------------------------------perspective --
 {Finds all tables containing a given column name or name fragment. Use when you
  need to change a column's parameters, but the Data Designer blocks changes
  'coz the columnName exists in one or more other tables. }

  ----------------------------------------------------------------environment --
  CLS
  SET ERROR VARIABLE vget_errorVar

  ------------------------------------------------------------------------vars--
  SET VAR vget_dKey TEXT = NULL
  SET VAR vget_dCaption TEXT = NULL
  SET VAR vget_dMessage TEXT = NULL
  SET VAR vget_dOperator TEXT = NULL
  SET VAR vget_dResponse1 TEXT = NULL
  SET VAR vget_dResponse2 TEXT = NULL
  SET VAR vget_amp_whereClause TEXT = NULL
  SET VAR vget_amp_outputCaption TEXT = NULL

  ------------------------------------------------------------string selector --
  SET VAR vget_dCaption = ' Find Table Names'
  SET VAR vget_dMessage = ' Search for ...'

  DIALOG .vget_dMessage vget_dResponse1 vget_dKey YES +
  CAPTION .vget_dCaption +
  OPTION THEMENAME longhorn +
  |MESSAGE_FONT_NAME calibri +
  |MESSAGE_FONT_SIZE 11 +
  |BUTTON_YES_FONT_NAME calibri +
  |BUTTON_YES_FONT_SIZE 11 +
  |BUTTON_YES_WIDTH 100 +
  |BUTTON_YES_CAPTION FULL NAME +
  |BUTTON_NO_FONT_NAME calibri +
  |BUTTON_NO_FONT_SIZE 11 +
  |BUTTON_NO_WIDTH 100 +
  |BUTTON_NO_CAPTION FRAGMENT

  IF vget_dResponse1 = 'YES' THEN
    SET VAR vget_operator = ' = '
    SET VAR vget_dMessage = 'Enter the full column name'
    SET VAR vget_eMessage = 'Name not found as entered'
  ELSE
    SET VAR vget_operator = ' CONTAINS '
    SET VAR vget_dMessage = 'Enter the name fragment'
    SET VAR vget_eMessage = 'Fragment not found as entered'
  ENDIF

  --------------------------------------------------------------select target --
  DIALOG .vget_dMessage vget_dResponse2=20 vget_dKey 1 +
  CAPTION .vget_dCaption +
  OPTION THEMENAME longhorn +
  |MESSAGE_FONT_NAME calibri +
  |MESSAGE_FONT_SIZE 11 +
  |BUTTON_OK_FONT_NAME calibri +
  |BUTTON_OK_FONT_SIZE 11 +
  |BUTTON_OK_CAPTION OK

  IF vget_dResponse2 IS NULL OR vget_dKey = '[Esc]' THEN ; GOTO cleanup ; ENDIF

  ------------------------------------------------------------------Do the Do --
  SET VAR vget_amp_outputCaption = +
    ( '|CAPTION Host Tables for Column: ' + (ULC(.vget_dResponse2)) )

  SET VAR vget_amp_whereClause = +
    ('WHERE t1.sys_table_id = t2.sys_table_ID AND ' + +
     't2.sys_column_name ' + .vget_operator & '''' + .vget_dResponse2 + '''' )

  SET ERROR MESSAGE 2059 OFF
  BROWSE DISTINCT +
    t1.sys_table_id, +
    t1.sys_table_name, +
    t1.sys_num_rows +
  FROM +
    sys_tables t1, +
    sys_columns t2 +
    &vget_amp_whereClause AND +
    t1.sys_table_name NOT LIKE 'SYS_%' +
  ORDER BY t1.sys_table_name +
  OPTION WINDOW_STATE NORMAL|ZEBRA_STRIPE ON +
    |TOP 50|LEFT 250|HEIGHT 600|WIDTH 500 +
    |COLUMN_WIDTH[0] 90 +
    |COLUMN_WIDTH[1] 280 +
    |COLUMN_WIDTH[2] 155 +
    &vget_amp_outputCaption
  SET VAR vget_error = .vget_errorVar
  SET ERROR MESSAGE 2059 ON

  -------------------------------------------------------------error handling --
  IF vget_error <> 0 THEN ; PAUSE 1 USING .vget_eMessage ; ENDIF

  --------------------------------------------------------------------cleanup --
  LABEL cleanup
  SET ERROR VARIABLE OFF
  CLEAR VAR vget%
  CLS

------------------------------------------------------------------end process --
  LABEL endProcess
  RETURN

