Interesting. Great minds think alike. A while back I wrote a utility to do just this, since I had a column that was also a primary key that needed to be larger and had a large number of sub-tables with foreign keys. In the old days when storage space was scarce, we sized the columns to the smallest size to save disk space; this is obviously no longer an issue, and now I pad columns to accommodate future needs.
My code asked for the columns name and then rename the column name on all the sub-tables, one at the time, change the length on the main table and then changed back the names of the column in all the sub-tables back to the original with the new length…or something along these lines. It took some work to get the code to work correctly, but once completed, worked like a charm, and saved a lot of time. I believe I posted the approach here and emailed the code to the people that requested it. I am sure I have it stored somewhere in my server, I will try to find it and we can compare approaches. It would be nice to have a code repository, like GitHub, where developers/users can upload utilities that others can download and save time and effort. Javier, Javier Valencia, PE 14315 S Twilight Ln Olathe, KS 66062 913-915-3137 From: [email protected] <[email protected]> On Behalf Of Steve Sweeney Sent: Tuesday, January 21, 2025 11:13 PM To: [email protected] Subject: RE: [RBASE-L] - Column definition Robert, For 40 years I wanted to write this routine – your post inspired me to try something today. WARNING: Before executing the routine below be sure to make a backup of your database. You may also want to run it offline as a test before updating the “working” copy of your data. Fair warning … 😊 It seems to work very well on “stand alone” columns – that is, columns that are not indexed, not in computed columns, not used in constraints, etc. If you have a simple TEXT column that needs to be expanded this routine might do the trick. Steve Sweeney San Ramon, CA -- ExpandTextColWidth.RMD -- NOTE: This is a rather simple routine that will EXPAND the size of TEXT "stand alone" columns -- NOTE: Be sure there are no CONSTRAINTS or COMPUTED COLUMNS or INDEXES as these types of columns may require manual changes -- Find the TEXT columns that are used in multiple tables --SELECT SYS_COLUMN_NAME=50,COUNT(*)=19 AS Number_of_Instances FROM SYS_COLUMNS WHERE SYS_TYPE_NAME = 'TEXT' GROUP BY SYS_COLUMN_NAME HAVING COUNT(*) > 1 -- Columns will be temporarily renamed as the ALTER TABLE statement expands the size SET V vSYS_COLUMN_PREFIX TEXT = 'Unique123Prefix456' -- Enter the COLUMN NAME you want to expand SET V vSYS_COLUMN_NAME TEXT = 'CageNum' -- This concatonates the "Prefix" with the "Column Name" to create a temporary column name SET V vtmp_COLUMN_NAME TEXT = (.vSYS_COLUMN_PREFIX + .vSYS_COLUMN_NAME) -- Here is the NEW column width SET V vSYS_COLUMN_LENGTH INTEGER = 48 -- Initialize a variable for the TABLE NAME SET V vSYS_TABLE_NAME TEXT = NULL -- Cursor to go through each instance of a TableName / ColumnName DECLARE cColNameCursor CURSOR FOR SELECT syst.SYS_TABLE_NAME,sysc.SYS_COLUMN_NAME FROM SYS_COLUMNS sysc , SYS_TABLES syst WHERE sysc.SYS_COLUMN_NAME = .vSYS_COLUMN_NAME AND sysc.SYS_TABLE_ID = syst.SYS_TABLE_ID OPEN cColNameCursor FETCH cColNameCursor INTO vSYS_TABLE_NAME,vSYS_COLUMN_NAME WHILE SQLCODE <> 100 THEN WRITE 'Table:' .vSYS_TABLE_NAME , 'Column:' .vSYS_COLUMN_NAME ALTER TABLE &vSYS_TABLE_NAME ALTER COLUMN &vSYS_COLUMN_NAME TO &vtmp_COLUMN_NAME TEXT &vSYS_COLUMN_LENGTH WRITE ' ' FETCH cColNameCursor INTO vSYS_TABLE_NAME,vSYS_COLUMN_NAME ENDWHILE -- Drop the cursor after execution DROP CURSOR cColNameCursor -- Rename the "TemporaryColumn" back to the "OriginalColumn" RENAME COLUMN &vtmp_COLUMN_NAME TO &vSYS_COLUMN_NAME RETURN From: [email protected] <mailto:[email protected]> <[email protected] <mailto:[email protected]> > On Behalf Of Bruce Chitiea Sent: Tuesday, January 21, 2025 3:57 PM To: [email protected] <mailto:[email protected]> Subject: Re: [RBASE-L] - Column definition Robert: The fastest, simplest way to do it is to do an UNLOAD ALL to a text file, then modify the column definitions in the text file OUTPUT utility_changeColumnWidth.rmd UNLOAD ALL OUTPUT SCREEN RBEDIT utility_changeColumnWidth.rmd ... make all your changes, save, and then: 1) Change the extensions of your existing .rx? file e.g: database.rx1.old ... .rx4.old 2) RUN utility_changeColumnWidth.rmd Done, and, you end up with a free backup of your database. Best, Bruce Bruce A. Chitiea SafeSectors, Inc. 1142 S Diamond Bar Blvd # 442 Diamond Bar CA 91765-2203 [email protected] <mailto:[email protected]> (909) 238-9012 m ------ Original Message ------ >From "'Robert Thompson' via RBASE-L" <[email protected] ><mailto:[email protected]> > To [email protected] <mailto:[email protected]> Date 1/21/2025 3:48:51 PM Subject [RBASE-L] - Column definition I have "Column A" defined as TEXT 12 in about 20 tables. I now need to expand that to TEXT 25. I know how to manually make the changes and it is not very efficient, so I thought I would ask if there is an option or process that I may have missed in some update over the years! So.... is there a straight forward way of changing a column text length definition across the database? Thanks, -- Robert Thompson TTC. Inc. -- 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/ef51c380-731b-4e0e-8b2e-886327601e4f%40comcast.net <https://groups.google.com/d/msgid/rbase-l/ef51c380-731b-4e0e-8b2e-886327601e4f%40comcast.net?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] <mailto:[email protected]> . To view this discussion visit https://groups.google.com/d/msgid/rbase-l/em71657adc-3f99-4299-887e-1a6e606134d2%40828a53f2.com <https://groups.google.com/d/msgid/rbase-l/em71657adc-3f99-4299-887e-1a6e606134d2%40828a53f2.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] <mailto:[email protected]> . To view this discussion visit https://groups.google.com/d/msgid/rbase-l/SN6PR03MB37271D896A55C3B8B118472889E12%40SN6PR03MB3727.namprd03.prod.outlook.com <https://groups.google.com/d/msgid/rbase-l/SN6PR03MB37271D896A55C3B8B118472889E12%40SN6PR03MB3727.namprd03.prod.outlook.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/46ae01db6d0c%247150d6c0%2453f28440%24%40vtgonline.com.
