MySQL: Bulk PK Rename

MySQL: Bulk PK Rename

SELECT CONCAT(
		GROUP_CONCAT('ALTER TABLE `',a.TABLE_NAME,'`',
		' CHANGE `',a.TABLE_NAME,'`.`',a.COLUMN_NAME,'` ',
		'`',a.TABLE_NAME,'`.`id` ',a.COLUMN_TYPE
	SEPARATOR ';n')
) AS q
FROM `information_schema`.`COLUMNS` a
WHERE a.`TABLE_SCHEMA` = 'target_database' AND a.`COLUMN_KEY` = 'PRI';

Copy & Paste the result in your MySQL editor and execute the SQL.

References
  1. http://stackoverflow.com/questions/7691816/group-concat-comma-separator-mysql
  2. http://stackoverflow.com/questions/893874/mysql-determine-tables-primary-key-dynamically/893922#893922
  3. http://mysql-0v34c10ck.blogspot.com/2011/05/better-way-to-get-primary-key-columns.html

Leave a Reply

Your email address will not be published. Required fields are marked *

eleven + 5 =