Mike;
I while back I wrote a utility to do just what you need. I believe that it
worked correctly on 6.5 but I am not sure if I have used in the newer
version since then, so, I cannot vouch for it. You can look at the code and
you can get ideas on how to automate your own code. I seem to remember that
you had to remove the indices for it to work correctly. One of these days I
will review it to add more features and to insure compatibility with the
current version. In the mean time, feel free to use it and/or change it to
suit your need. Before you start playing with it, make sure that you have
current and verified backups!!!
>>>>>>>>>>>>>>>>>>>>>
*(
Change_txt_col_lgt.rmd - Change the length of a text column
Written by: Javier Valencia on 01/15/2004
Last Modified by: Javier Valencia on 01/15/2004
This utility will change the lenght of a text column where the column
is defined in more than one table.
The code uses a View V_table_column which is a combination of the:
SYS_TABLES and SYS_COLUMNS linked by the SYS_TABLE_ID common column
The view definition is as follows:
SYS_COLUMNS.SYS_DATA_TYPE, SYS_COLUMNS.SYS_LENGTH
FROM SYS_TABLES sys_tables, SYS_COLUMNS
WHERE sys_tables.SYS_TABLE_ID = SYS_COLUMNS.SYS_TABLE_ID AND
sys_tables.SYS_TABLE_TYPE = 'TABLE'
ORDER BY sys_tables.SYS_TABLE_NAME ASC, SYS_COLUMNS.SYS_COLUMN_NAME ASC
The RENAME and ALTER commands do not work when there is an open CURSOR
so the CURSOR needs to be opened and droped several times
)
CLEAR VAR vcolname,vrows,vnewlgt,vendkey
SET VAR vcaption TEXT = ' STEP 1 - Select Column'
SET VAR vcolname TEXT = NULL
SET VAR voldlgt INTEGER = NULL
LABEL start
-- select TEXT columns from tables (not views or system tables)
-- The SYS_TABLE_TYPE = 'TABLE' is redundant as it is in view definition
-- You can include SYS_DATA_TYPE = 3 (text column) in the view definition
and
-- do away with the WHERE clause all together.
CHOOSE vcolname FROM #VALUES FOR DISTINCT sys_column_name +
FROM v_table_column +
WHERE sys_table_type = 'TABLE' AND sys_data_type IN (3,-3) +
CAPTION .vcaption LINES 10
IF vcolname = '[Esc]' THEN
GOTO done
ENDIF
-- Get the current text length
SET VAR voldlgt = sys_length IN sys_columns WHERE sys_column_name =
.vcolname
-- Get the new text length
SET VAR vcaption TEXT = ' STEP 2 - Select New Length'
SET VAR vmsg TEXT = ('Current Length is: ' + CTXT(.voldlgt) + +
(CHAR(013))+ 'Enter New Lenght:' )
SET VAR vtnewlgt TEXT = NULL
--SET VAR viNewLgt INTEGER = 0
SET VAR vrows INTEGER = NULL
DIALOG .vmsg vtnewlgt=4 vendkey 1 +
CAPTION vcaption ICON APP +
OPTION TITLE_FONT_COLOR BLACK +
|TITLE_BACK_COLOR WHITE +
|TRANSPARENCY 255 +
|WINDOW_BACK_COLOR WHITE
IF vendkey = '[Esc]' OR vtnewlgt IS NULL THEN
GOTO done
ENDIF
-- check to see how many tables have this column, if only one, use DB
Designer
-- if not ask for confirmation
SELECT COUNT(*) INTO vrows INDICATOR ivrows +
FROM v_table_column WHERE sys_column_name = .vcolname
IF vrows = 1 THEN
PAUSE 2 USING +
'Only 1 column with this name, use DBDESIGN to change lenght!' +
CAPTION .vcaption ICON warning +
OPTION BACK_COLOR WHITE +
|MESSAGE_COLOR RED +
|MESSAGE_FONT_COLOR WHITE
GOTO done
ELSE
CLS
SET VAR vmsg = +
('Column:'+(CHAR(009))+(CHAR(009)) & .vcolname + (CHAR(013))+ +
'Current Length:' + (CHAR(009))& CTXT(.voldlgt) + (CHAR(013))+ +
'New Length:' + (CHAR(009)) & .vtnewlgt + (CHAR(013))+ +
'Tables:' + (CHAR(009)) & CTXT(.vrows) + (CHAR(013)))
CLS
DIALOG .vmsg vyesno vendkey yes CAPTION ' Step 3 - Confirm Everything'
ICON APP +
OPTION TITLE_FONT_COLOR BLACK +
|TITLE_BACK_COLOR WHITE +
|TRANSPARENCY 255 +
|WINDOW_BACK_COLOR WHITE +
|BUTTON_YES_CAPTION &Yes +
|BUTTON_NO_CAPTION &No +
|BUTTON_YES_COLOR GREEN +
|BUTTON_NO_COLOR RED +
|BUTTON_YES_FONT_COLOR WHITE +
|BUTTON_NO_FONT_COLOR WHITE
IF vendkey = '[Esc]' OR vyesno = 'No' THEN
GOTO done
ENDIF
SET ERROR MESSAGES 705 OFF
DROP CURSOR ptr2
SET ERROR MESSAGES 705 ON
SET VAR vtemp1colname = (.vcolname + '_T1')
SET VAR vtemp2colname = (.vcolname + '_T2')
-- Rename the columns to a temporary name
RENAME COLUMN &vcolname TO &vtemp1colname NOCHECK
-- Find the first table with this column
DECLARE ptr2 CURSOR FOR SELECT sys_table_name FROM v_table_column +
WHERE sys_column_name = .vtemp1colname
OPEN ptr2
SET VAR SQLCODE = 0
FETCH ptr2 INTO vtablename
DROP CURSOR ptr2
-- Rename the temporary column name to the oruginal column name
RENAME COLUMN &vtemp1colname TO &vcolname IN &vtablename NOCHECK
-- Change the TEXT Length to the new length
SET VAR vcomm = ('ALTER TABLE ' + .vtablename + ' ALTER COLUMN ' + +
.vcolname + ' TO ' + .vcolname + ' TEXT ' + .vtnewlgt)
&vcomm
-- The RENAME and ALTER command will not work with an open cursor
-- so the procedure will need to be repeated until there are no
-- more tables to be updated
LABEL do_again
DECLARE ptr2 CURSOR FOR SELECT sys_table_name FROM v_table_column +
WHERE sys_column_name = .vtemp1colname
OPEN ptr2
SET VAR SQLCODE = 0
FETCH ptr2 INTO vtablename
IF SQLCODE = 100 THEN
DROP CURSOR ptr2
GOTO end_do_again
ENDIF
DROP CURSOR ptr2
-- Rename the next occurrence to a second temporary name,
-- change it TEXT size and rename it back to the original name
RENAME COLUMN &vtemp1colname TO &vtemp2colname IN &vtablename NOCHECK
SET VAR vcomm = ('ALTER TABLE ' + .vtablename + ' ALTER COLUMN ' + +
.vtemp2colname + ' TO ' + .vtemp2colname + ' TEXT ' + .vtnewlgt)
&vcomm
RENAME COLUMN &vtemp2colname TO &vcolname IN &vtablename NOCHECK
SET VAR SQLCODE = 0
GOTO do_again
LABEL end_do_again
PAUSE 2 USING 'Columns Updated!' +
CAPTION .vcaption ICON warning +
OPTION BACK_COLOR WHITE +
|MESSAGE_COLOR RED +
|MESSAGE_FONT_COLOR WHITE
GOTO done
ENDIF
LABEL done
CLEAR VAR
vcaption,vcolname,vcomm,vendkey,vinewlgt,vmsg,voldlgt,vrows,vtempcolname,vye
sno,vtablename,vtnewlgt,vmsg
RETURN
<<<<<<<<<<<<<<<<<<<<
Good luck
Javier,
Javier Valencia
913-915-3137
________________________________________
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of mike epstein
sony viao office
Sent: Saturday, November 08, 2008 10:15 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - changing the length of a text column common to other
tables
Good Morning,
It was a pleasure to put faces with names at the recent convention in
Pittsburg.
I am trying to change the length of a text value column in a database where
there are prox 10 tables which contain the identical column name .
It seems that all of the tables must be changed simultaneously.
Any ideas out there?
Mike Epstein (Joe the plumbers distant cousin)
