sql_alter_table
NAME
sql_alter_table: Generate ALTER TABLE SQL statements per table, with engine and create optionsTYPE
ViewDESCRIPTION
sql_alter_table provides with SQL statements to alter a table to its current form.
This view analyzes table structure and provides with the syntax required to rebuild the table by various aspects. It can be useful in generating a "resurrection" script to restore table to its current engine or indexes state. For example, it may provide with the rollback script for a database migration from MyISAM to InnoDB, or from InnoDB Antelope to Barracuda format.
STRUCTURE
mysql> DESC common_schema.sql_alter_table; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | ENGINE | varchar(64) | YES | | NULL | | | sql_drop_keys | longtext | YES | | NULL | | | sql_add_keys | longblob | YES | | NULL | | | table_options | varchar(327) | YES | | NULL | | | alter_statement | varchar(511) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+
SYNOPSIS
Columns of this view:
- TABLE_SCHEMA: schema of current table
- TABLE_NAME: current table name
- ENGINE: current engine name
- sql_drop_keys: A SQL statement to drop all keys on table.
- sql_add_keys: A SQL statement to create all keys on table
- table_options: All table options for this table
- alter_statement:
A SQL statement which ALTERs current table to its current engine with create options.
Use eval() to apply SQL columns.
The SQL statements are not terminated by ';'.
EXAMPLES
Generate ALTER TABLE statements for `sakila` tables:
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, alter_statement FROM common_schema.sql_alter_table WHERE TABLE_SCHEMA='sakila'; +--------------+---------------+--------+-----------------------------------------------------+ | TABLE_SCHEMA | TABLE_NAME | ENGINE | alter_statement | +--------------+---------------+--------+-----------------------------------------------------+ | sakila | actor | InnoDB | ALTER TABLE `sakila`.`actor` ENGINE=InnoDB | | sakila | address | InnoDB | ALTER TABLE `sakila`.`address` ENGINE=InnoDB | | sakila | category | InnoDB | ALTER TABLE `sakila`.`category` ENGINE=InnoDB | | sakila | city | InnoDB | ALTER TABLE `sakila`.`city` ENGINE=InnoDB | | sakila | country | InnoDB | ALTER TABLE `sakila`.`country` ENGINE=InnoDB | | sakila | customer | InnoDB | ALTER TABLE `sakila`.`customer` ENGINE=InnoDB | | sakila | film | InnoDB | ALTER TABLE `sakila`.`film` ENGINE=InnoDB | | sakila | film_actor | InnoDB | ALTER TABLE `sakila`.`film_actor` ENGINE=InnoDB | | sakila | film_category | InnoDB | ALTER TABLE `sakila`.`film_category` ENGINE=InnoDB | | sakila | film_text | MyISAM | ALTER TABLE `sakila`.`film_text` ENGINE=MyISAM | | sakila | inventory | InnoDB | ALTER TABLE `sakila`.`inventory` ENGINE=InnoDB | | sakila | language | InnoDB | ALTER TABLE `sakila`.`language` ENGINE=InnoDB | | sakila | payment | InnoDB | ALTER TABLE `sakila`.`payment` ENGINE=InnoDB | | sakila | rental | InnoDB | ALTER TABLE `sakila`.`rental` ENGINE=InnoDB | | sakila | staff | InnoDB | ALTER TABLE `sakila`.`staff` ENGINE=InnoDB | | sakila | store | InnoDB | ALTER TABLE `sakila`.`store` ENGINE=InnoDB | +--------------+---------------+--------+-----------------------------------------------------+
Modify tables; again generate ALTER TABLE statements for `sakila` tables:
mysql> ALTER TABLE sakila.film_text ENGINE=MyISAM ROW_FORMAT=FIXED; mysql> ALTER TABLE sakila.film_category ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; mysql> SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, alter_statement FROM common_schema.sql_alter_table WHERE TABLE_SCHEMA='sakila'; +--------------+---------------+--------+-------------------------------------------------------------------------------------------+ | TABLE_SCHEMA | TABLE_NAME | ENGINE | alter_statement | +--------------+---------------+--------+-------------------------------------------------------------------------------------------+ | sakila | actor | InnoDB | ALTER TABLE `sakila`.`actor` ENGINE=InnoDB | | sakila | address | InnoDB | ALTER TABLE `sakila`.`address` ENGINE=InnoDB | | sakila | category | InnoDB | ALTER TABLE `sakila`.`category` ENGINE=InnoDB | | sakila | city | InnoDB | ALTER TABLE `sakila`.`city` ENGINE=InnoDB | | sakila | country | InnoDB | ALTER TABLE `sakila`.`country` ENGINE=InnoDB | | sakila | customer | InnoDB | ALTER TABLE `sakila`.`customer` ENGINE=InnoDB | | sakila | film | InnoDB | ALTER TABLE `sakila`.`film` ENGINE=InnoDB | | sakila | film_actor | InnoDB | ALTER TABLE `sakila`.`film_actor` ENGINE=InnoDB | | sakila | film_category | InnoDB | ALTER TABLE `sakila`.`film_category` ENGINE=InnoDB row_format=COMPRESSED KEY_BLOCK_SIZE=8 | | sakila | film_text | MyISAM | ALTER TABLE `sakila`.`film_text` ENGINE=MyISAM row_format=FIXED | | sakila | inventory | InnoDB | ALTER TABLE `sakila`.`inventory` ENGINE=InnoDB | | sakila | language | InnoDB | ALTER TABLE `sakila`.`language` ENGINE=InnoDB | | sakila | payment | InnoDB | ALTER TABLE `sakila`.`payment` ENGINE=InnoDB | | sakila | rental | InnoDB | ALTER TABLE `sakila`.`rental` ENGINE=InnoDB | | sakila | staff | InnoDB | ALTER TABLE `sakila`.`staff` ENGINE=InnoDB | | sakila | store | InnoDB | ALTER TABLE `sakila`.`store` ENGINE=InnoDB | +--------------+---------------+--------+-------------------------------------------------------------------------------------------+
Note again that the SQL statements are not terminated by ';'. Either CONCAT() these beforehand, or use sed/awk afterwards.
Show indexes DROP and ADD statements for a given table:
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, sql_drop_keys, sql_add_keys FROM common_schema.sql_alter_table WHERE TABLE_SCHEMA='sakila' and TABLE_NAME='rental' \G TABLE_SCHEMA: sakila TABLE_NAME: rental sql_drop_keys: DROP KEY `idx_fk_customer_id`, DROP KEY `idx_fk_inventory_id`, DROP KEY `idx_fk_staff_id`, DROP PRIMARY KEY, DROP KEY `rental_date` sql_add_keys: ADD KEY `idx_fk_customer_id`(`customer_id`), ADD KEY `idx_fk_inventory_id`(`inventory_id`), ADD KEY `idx_fk_staff_id`(`staff_id`), ADD PRIMARY KEY (`rental_id`), ADD UNIQUE KEY `rental_date`(`rental_date`,`inventory_id`,`customer_id`)