Really cool piece of code, Bruce! Steve
From: [email protected] <[email protected]> On Behalf Of Bruce Chitiea Sent: Wednesday, December 4, 2024 9:56 AM To: [email protected] Subject: [RBASE-L] - Utility | Find Table Containing ColumnName Giving Tuesday Plus One The following utility may be of use to some. Credit for an earlier iteration of the core code is due someone on the old R:List, name lost to time. Reworked and spiffed up a bit. Bruce {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 -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. To view this discussion visit https://groups.google.com/d/msgid/rbase-l/emad540fdc-d0a6-43eb-9356-cef8eaa58216%402164b13e.com<https://groups.google.com/d/msgid/rbase-l/emad540fdc-d0a6-43eb-9356-cef8eaa58216%402164b13e.com?utm_medium=email&utm_source=footer>. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion visit https://groups.google.com/d/msgid/rbase-l/BYAPR03MB3718C569A966299552BDDBB189372%40BYAPR03MB3718.namprd03.prod.outlook.com.
