Here is the code I wrote; I created it because I had a data base with lots of
text columns that needed to be resized and I figured in the long term it would
save time over doing it manually. As you can see it is quite old and I believe
I had a newer version where I checked for the system view needed and if not
available it created it; you can easily add the code to do this or simply
create the view ahead of time. I have not used this code in a long time or with
the latest versions so I am not sure if it still works as originally designed.
You might to make a few changes but the majority of the code should work
correctly; I am sure you can make changes as needed.
As with all utilities, please be sure to back up your data base before running
the utility just in case something gets corrupted you can get the original
version back.
I make no promises that the code will work as originally designed and you can
use at your own risk. Hopefully it will be of use to you or anyone else that
needs it.
*(
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 length 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,vyesno,vtablename,vtnewlgt,vmsg
RETURN
Javier,
Javier Valencia, PE
O: 913-829-0888
H: 913-397-9605
C: 913-915-3137
From: [email protected] [mailto:[email protected]] On Behalf Of
Javier Valencia
Sent: Wednesday, November 02, 2016 1:02 PM
To: [email protected]
Subject: RE: [RBASE-L] - Column size change
I keep the last 2 or 3 version on my computer but the older ones are archived
and obviously this utility is several years old so I did not find among the
files currently in my computer. I will do a search of the server backups for
the older versions and hopefully I will find it there.
Javier,
Javier Valencia, PE
O: 913-829-0888
H: 913-397-9605
C: 913-915-3137
From: [email protected] [mailto:[email protected]] On Behalf Of
[email protected]
Sent: Wednesday, November 02, 2016 9:17 AM
To: [email protected]
Subject: RE: [RBASE-L] - Column size change
Thank You Tony and Javier for the responses.
As Jim said, if someone does find the program please post or send.
Cheers
Anne
-------- Original Message --------
Subject: RE: [RBASE-L] - Column size change
From: "Javier Valencia" < <mailto:[email protected]>
[email protected]>
Date: Tue, November 01, 2016 1:01 pm
To: < <mailto:[email protected]> [email protected]>
Anne,
First you have to rename the Foreign keys say ColumnA to ColumnAx, then resize
the Primary key and then change the name back of the Foreign keys to the new
name and the columns will take on the new size.
I wrote a program that does it all for you, asks you which TEXT column you want
to re-seize and what size you want it to be and then it goes through the
process above, identifying the Primary and Foreign keys and resizing
accordingly and in the proper sequence. I have not used it in a long time and I
will see if I can find it. I now I sent it to several member before and maybe
someone has it handy.
Javier,
Javier Valencia, PE
O: 913-829-0888
H: 913-397-9605
C: 913-915-3137
From: <mailto:[email protected]> [email protected] [
<mailto:[email protected]> mailto:[email protected]] On Behalf Of
<mailto:[email protected]> [email protected]
Sent: Tuesday, November 01, 2016 11:30 AM
To: <mailto:[email protected]> [email protected]
Subject: [RBASE-L] - Column size change
Hello!
Is there a command that would increase the size of a column that exists in
multiple tables, or
is the manual way the only way?
Thank You
Anne
--
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 <mailto:[email protected]>
[email protected].
For more options, visit <https://groups.google.com/d/optout>
https://groups.google.com/d/optout.
--
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 <mailto:[email protected]>
[email protected].
For more options, visit <https://groups.google.com/d/optout>
https://groups.google.com/d/optout.
--
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 <mailto:[email protected]>
[email protected].
For more options, visit <https://groups.google.com/d/optout>
https://groups.google.com/d/optout.
--
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].
For more options, visit https://groups.google.com/d/optout.
--
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].
For more options, visit https://groups.google.com/d/optout.
