1

I have many tables with columns of varchar(45). Now I need them all to became varchar(128). Is there a way to do it without manually examining all 40 tables?

I found this question that touches similar topic, but how to do it with columns?

Community
  • 1
  • 1
Mołot
  • 699
  • 12
  • 36
  • Didn't meant to answer it myself, but while writing question I somehow simply did it, so sharing. – Mołot Sep 12 '13 at 13:53

1 Answers1

3

To obtain all columns that meets my needs, I can use:

SELECT table_name, column_name FROM information_schema.columns 
  WHERE table_schema LIKE "my_schema_name" 
    AND data_type = "varchar"
    AND character_maximum_length < 50;

Now I need to use any programming language that can connect to MySQL, and in a loop execute:

ALTER TABLE table_name MODIFY column_name VARCHAR(128);
Mołot
  • 699
  • 12
  • 36
  • 1
    why did your question say `varchar(256)` but your answer has `VARCHAR(128)`??... anyways good answer. – amaster Sep 12 '13 at 14:15
  • @amaster507 Corrected. Requirements changed between question and answer part and I missed it. Thanks for noticing. – Mołot Sep 12 '13 at 14:19
  • 1
    NOTE: when you use MODIFY, the column_definition must include all of the attributes that should apply to the new column (other than index attributes). Attributes present in the original definition but not specified for the new definition are not carried forward. For example, if one of those columns was defined as `NOT NULL`, then after the MODIFY in your statement, the column will "lose" the NOT NULL attribute. – spencer7593 Sep 12 '13 at 15:40
  • @spencer7593 Good point. I have 2 questions. 1) can you link the docs? With #anchor to that part? Maybe I'm mind-blind but I can't see it. 2) do you know a way around it? – Mołot Sep 12 '13 at 18:52
  • 1
    (1) [http://dev.mysql.com/doc/refman/5.5/en/alter-table.html](http://dev.mysql.com/doc/refman/5.5/en/alter-table.html) There's no anchor for that section, it's buried deep under the ninth bullet under Usage Notes. It's the only occurrence of the phrase "must include" on the page; In Firefox or Chrome -> Find (Ctrl+F) "must include". (2) there's no workaround, other than pulling the other attributes and producing the full column_definition. – spencer7593 Sep 12 '13 at 20:22
  • And don't forget to take a backup before trying this! – Paul Campbell Nov 17 '17 at 22:57