CODEWITHSUNDEEP
sqlmysql
Dónal
Dónal

Reputation: 187529

rename tables columns in mysql

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

Upvotes: 0

Views: 377

Answers (2)

Phill Pafford
Phill Pafford

Reputation: 85318

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

Upvotes: 0

longneck
longneck

Reputation: 12226

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%'

Upvotes: 1

Related Questions