rename tables columns in mysql

Question

Hi,

I would like to rename a table column in MySql and also have the name updated in any triggers, stored procs, etc. that reference the column. This does not appear to happen by default when using the alter table command.

If a tool exists that can perform such a refactoring, it would be great if it could generate an SQL script that applies these changes. This would then allow me to make the same changes on another DB (without using the tool again).

Thanks, Don

Answer 1

I have never used this but looks like a good option, Devart MySQL Administration. I don't know if you could use this for an existing project but it looks like an option to explore.

Also I did see this on Stack, could help as well

Answer 2

there isn't an automated way that i know of, but you can get a pretty authoritative list of procedures and triggers and views that use that column, provided you don't use * and you don't use dynamic SQL generation in your triggers/procedures/views.

SELECT 'sp' AS type
     , routine_schema
     , routine_name
  FROM information_schema.routines
 WHERE routine_body LIKE '%$table%'
   AND routine_body LIKE '%$column%'
 UNION ALL
SELECT 'trigger' AS type
     , trigger_schema
     , trigger_name
  FROM information_schema.triggers
 WHERE action_statement LIKE '%$table%'
   AND action_statement LIKE '%$column%'
 UNION ALL
SELECT 'view' AS type
     , table_schema
     , table_name
  FROM information_schema.views
 WHERE view_definition LIKE '%$table%'
   AND view_definition LIKE '%$column%'