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].
To view this discussion visit
https://groups.google.com/d/msgid/rbase-l/emad540fdc-d0a6-43eb-9356-cef8eaa58216%402164b13e.com.
