SQL: convert all columns to VarChar(255)

Question

After some searching here on stackoverflow and on the web, I couldn't find the answer to my question. I'm not a real SQL talent, but I'm trying to covert all the columns in my table to varchar (255). It has about 600 columns which are all varchar, but the size limit varies. I would like them all to be 255. Is there a way to not having to do this manually? I work with MySQL.

Thanks!

_"It has about 600 columns"_ How could that happen? Have you ever heard of [normalization](http://en.wikipedia.org/wiki/Database_normalization)? — Tim Schmelter, Aug 26 '13 at 15:18
Possible duplicate http://stackoverflow.com/questions/3773480/how-do-i-alter-table-column-datatype-on-more-than-1-column — user1234, Aug 26 '13 at 15:23

score 2 · Answer 1 · edited Aug 26 '13 at 15:30

2

This isn't what you really need to do. You have something more important to do: NORMALIZE YOUR DATABASE

Now, It's impossible that you have a normalized table with 600 columns. Split your entities in that table correctly, following at least the 3rd normal form rules. After that, you'll have a much better database which is easier to mantain.

To do this, you'll need to drop your current table, therefore, you don't need to change all the types to varchar(255) because you'll fix them during the creation of other tables.

This would be a good start to read: http://en.wikipedia.org/wiki/Database_normalization (thanks to @Tim Schmelter from question's comments)

edited Aug 26 '13 at 15:30

Kermit

33,827
13
85
121

answered Aug 26 '13 at 15:20

STT LCU

4,348
4
29
47

You've made me curious: I have a table with in the first column a category, that category has up to 599 matching keywords. So after ever category, the remaining columns each hold 1 keyword. How do you want to NORMALIZE that? – user2704687 Aug 26 '13 at 15:23
@user2704687 with three tables: one with the list of categories, one with the list of keywords, and the 3rd with category_id and keyword_id (you'd have 600 rows now, which is the expected behavior of a normalized DB). therefore, you can have much more than 600 categories, if that would ever happen. It's a simple N:M relationship. – STT LCU Aug 26 '13 at 15:25
1

Also, you're visualizing a database as an excel table: do not do that, Database tables aren't mere bidimensional tables. – STT LCU Aug 26 '13 at 15:27
@user2704687 The main advantages of the normalized solution is that it is easier to maintain (you do'nt need to update or insert 600 fields, you define your field length once and your queries will certainly be shorter), you're not limited to a determined number of entries and you can add metadata to your keyword (such as abbreviation, phonetic writing... in order to enhance lookup) – C.Champagne Aug 26 '13 at 16:14

dcaswell · Answer 2 · 2013-08-26T15:28:48.893

2

You need to generate the alter table statement by pulling the data from the database.

select 'alter table MyTableName modify column ' + column_name + ' varchar(255);'
from information_schema where table_name = 'MyTableName'

And then paste the results of this command into the query window and run it -- making sure it does what you want it to do. Do a backup first.

Or you could make one big alter statement (if MySql wouldn't choke on it) by replacing the semicolon with a comma.

edited Aug 26 '13 at 15:28

answered Aug 26 '13 at 15:27

dcaswell

3,137
2
26
25

Thanks. Fixed. I got fixated on the 600 number. – dcaswell Aug 26 '13 at 15:30
Warning. If a column was NOT NULL before, this code would make it NULL-able, an unintended side-effect. – Joshua Huber Apr 27 '17 at 20:04

peterm · Answer 3 · 2013-08-26T15:44:19.230

First of all as mentioned by others you better off normalize you data.

In the meantime you can achieve your goal with dynamic SQL like this

DELIMITER $$
CREATE PROCEDURE change_to_varchar255(IN _tname VARCHAR(64))
BEGIN
  SET @sql = NULL;

  SELECT GROUP_CONCAT( 
           CONCAT_WS(' ', 'CHANGE', COLUMN_NAME, COLUMN_NAME, 'VARCHAR(255)'))
    INTO @sql
    FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = _tname
     AND DATA_TYPE = 'varchar'
     AND CHARACTER_MAXIMUM_LENGTH < 255
     AND TABLE_SCHEMA = SCHEMA();

  SET @sql = CONCAT_WS(' ', 'ALTER TABLE', _tname, @sql);

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

Sample usage:

CALL change_to_varchar255('table1');

Here is SQLFiddle demo

Careful. If a column was NOT NULL before, this code would make it NULL-able, an unintended side-effect. — Joshua Huber, Apr 27 '17 at 20:02

score 0 · Answer 4 · answered Jan 26 '21 at 13:24

If you are using PhpMyAdmin or other, you can also click on the button to modify the table.

When you are on the web page, press Ctrl+Shift+J under Windows or Cmd+Opt+J under Mac to open the console window in the Chrome Developer tools. Now enter the following command to replace all occurrences of the number 255 with 100 :

document.body.innerHTML = document.body.innerHTML.replace(/255/g, "100").

Finally, click on the button to execute the query.

SQL: convert all columns to VarChar(255)

4 Answers4