technique
08-04-11, 10:08 AM
Here are several cleanup queries that are safe to run over your world database. They have been adjusted for my database, so you might have to edit something in case you receive an error upon executing it but I think I managed to correct column names to fit ArcEmu at least. If there's any questions don't hesitate to ask!
Credits: technique, Walla
UPDATE `creature_spawns` SET `flags` = '768', `bytes1` = '16843008', `bytes2` = '1' WHERE `entry` IN (SELECT `entry` FROM `creature_names` WHERE `name` LIKE '%Alliance Spirit Guide%' OR `name` LIKE '%Horde Spirit Guide%' OR `name` LIKE '%Taunka Spirit Guide%' OR `name` LIKE '%Dwarven Spirit Guide%');
UPDATE `creature_spawns` SET `flags` = '768', `bytes1` = '16843008', `bytes2` = '1' WHERE `entry` = '6491';
UPDATE `creature_proto` SET `walk_speed` = 2.5, `run_speed` = 8, `fly_speed` = '14';
UPDATE `creature_names` SET `flags1` = `flags1`|'108' WHERE `rank` = '3';
UPDATE `creature_waypoints` SET `forwardskinid` = (SELECT `displayid` FROM `creature_spawns` where `id` = `spawnid`);
UPDATE `creature_waypoints` SET `backwardskinid` = (SELECT `displayid` FROM `creature_spawns` where `id` = `spawnid`);
UPDATE `creature_spawns` SET `movetype` = '0' WHERE `id` NOT IN (SELECT `spawnid` FROM `creature_waypoints`);
ALTER TABLE `creature_spawns` AUTO_INCREMENT=1, ADD COLUMN `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, CHANGE COLUMN `id` `id2` INT(11) UNSIGNED NOT NULL AFTER `id`, DROP PRIMARY KEY, ADD PRIMARY KEY (`id`);
ALTER TABLE `creature_spawns` ADD INDEX `id2` (`id2`);
UPDATE `creature_waypoints`,creature_spawns SET `spawnid` = id where spawnid = id2;
UPDATE `creature_formations`,creature_spawns SET `spawn_id` = id where `spawn_id` = id2;
UPDATE `creature_formations`,creature_spawns SET `target_spawn_id` = id where `target_spawn_id` = id2;
ALTER TABLE `creature_spawns` DROP COLUMN `id2`;
ALTER TABLE `gameobject_spawns` AUTO_INCREMENT=1, ADD COLUMN `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, CHANGE COLUMN `id` `id2` INT(11) UNSIGNED NOT NULL AFTER `id`, DROP PRIMARY KEY, ADD PRIMARY KEY (`id`);
ALTER TABLE `gameobject_spawns` ADD INDEX `id2` (`id2`);
ALTER TABLE `gameobject_spawns` DROP COLUMN `id2`;
ALTER TABLE `loot_creatures` AUTO_INCREMENT=1, ADD COLUMN `index` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, CHANGE COLUMN `index` `index2` INT(11) UNSIGNED NOT NULL AFTER `index`, DROP PRIMARY KEY, ADD PRIMARY KEY (`index`);
ALTER TABLE `loot_creatures` ADD INDEX `index2` (`index2`);
ALTER TABLE `loot_creatures` DROP COLUMN `index2`;
ALTER TABLE `loot_disenchanting` AUTO_INCREMENT=1, ADD COLUMN `index` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, CHANGE COLUMN `index` `index2` INT(11) UNSIGNED NOT NULL AFTER `index`, DROP PRIMARY KEY, ADD PRIMARY KEY (`index`);
ALTER TABLE `loot_disenchanting` ADD INDEX `index2` (`index2`);
ALTER TABLE `loot_disenchanting` DROP COLUMN `index2`;
ALTER TABLE `loot_fishing` AUTO_INCREMENT=1, ADD COLUMN `index` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, CHANGE COLUMN `index` `index2` INT(11) UNSIGNED NOT NULL AFTER `index`, DROP PRIMARY KEY, ADD PRIMARY KEY (`index`);
ALTER TABLE `loot_fishing` ADD INDEX `index2` (`index2`);
ALTER TABLE `loot_fishing` DROP COLUMN `index2`;
ALTER TABLE `loot_gameobjects` AUTO_INCREMENT=1, ADD COLUMN `index` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, CHANGE COLUMN `index` `index2` INT(11) UNSIGNED NOT NULL AFTER `index`, DROP PRIMARY KEY, ADD PRIMARY KEY (`index`);
ALTER TABLE `loot_gameobjects` ADD INDEX `index2` (`index2`);
ALTER TABLE `loot_gameobjects` DROP COLUMN `index2`;
ALTER TABLE `loot_items` AUTO_INCREMENT=1, ADD COLUMN `index` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, CHANGE COLUMN `index` `index2` INT(11) UNSIGNED NOT NULL AFTER `index`, DROP PRIMARY KEY, ADD PRIMARY KEY (`index`);
ALTER TABLE `loot_items` ADD INDEX `index2` (`index2`);
ALTER TABLE `loot_items` DROP COLUMN `index2`;
ALTER TABLE `loot_pickpocketing` AUTO_INCREMENT=1, ADD COLUMN `index` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, CHANGE COLUMN `index` `index2` INT(11) UNSIGNED NOT NULL AFTER `index`, DROP PRIMARY KEY, ADD PRIMARY KEY (`index`);
ALTER TABLE `loot_pickpocketing` ADD INDEX `index2` (`index2`);
ALTER TABLE `loot_pickpocketing` DROP COLUMN `index2`;
ALTER TABLE `loot_skinning` AUTO_INCREMENT=1, ADD COLUMN `index` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, CHANGE COLUMN `index` `index2` INT(11) UNSIGNED NOT NULL AFTER `index`, DROP PRIMARY KEY, ADD PRIMARY KEY (`index`);
ALTER TABLE `loot_skinning` ADD INDEX `index2` (`index2`);
ALTER TABLE `loot_skinning` DROP COLUMN `index2`;
DELETE FROM `ai_agents` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `creature_formations` WHERE `spawn_id` NOT IN (SELECT `id` FROM `creature_spawns`);
DELETE FROM `creature_formations` WHERE `target_spawn_id` NOT IN (SELECT `id` FROM `creature_spawns`);
DELETE FROM `creature_names_localized` WHERE `id` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `creature_proto` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `creature_quest_finisher` WHERE `id` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `creature_quest_finisher` WHERE `quest` NOT IN (SELECT `quest` FROM `quests`);
DELETE FROM `creature_quest_starter` WHERE `id` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `creature_quest_starter` WHERE `quest` NOT IN (SELECT `quest` FROM `quests`);
DELETE FROM `creature_spawns` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `creature_spawns` WHERE `map` NOT IN (SELECT `entry` FROM `worldmap_info`);
DELETE FROM `creature_staticspawns` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `creature_staticspawns` WHERE `map` NOT IN (SELECT `entry` FROM `worldmap_info`);
DELETE FROM `creature_timed_emotes` WHERE `spawnid` NOT IN (SELECT `id` FROM `creature_spawns`);
DELETE FROM `creature_waypoints` WHERE `spawnid` NOT IN (SELECT `id` FROM `creature_spawns`);
DELETE FROM `gameobject_names_localized` WHERE `entry` NOT IN (SELECT `entry` FROM `gameobject_names`);
DELETE FROM `gameobject_quest_finisher` WHERE `id` NOT IN (SELECT `entry` FROM `gameobject_names`);
DELETE FROM `gameobject_quest_finisher` WHERE `quest` NOT IN (SELECT `quest` FROM `quests`);
DELETE FROM `gameobject_quest_item_binding` WHERE `entry` NOT IN (SELECT `entry` FROM `gameobject_names`);
DELETE FROM `gameobject_quest_item_binding` WHERE `quest` NOT IN (SELECT `quest` FROM `quests`);
DELETE FROM `gameobject_quest_item_binding` WHERE `item` NOT IN (SELECT `entry` FROM `items`);
DELETE FROM `gameobject_quest_pickup_binding` WHERE `entry` NOT IN (SELECT `entry` FROM `gameobject_names`);
DELETE FROM `gameobject_quest_pickup_binding` WHERE `quest` NOT IN (SELECT `quest` FROM `quests`);
DELETE FROM `gameobject_quest_starter` WHERE `id` NOT IN (SELECT `entry` FROM `gameobject_names`);
DELETE FROM `gameobject_quest_starter` WHERE `quest` NOT IN (SELECT `quest` FROM `quests`);
DELETE FROM `gameobject_spawns` WHERE `entry` NOT IN (SELECT `entry` FROM `gameobject_names`);
DELETE FROM `gameobject_spawns` WHERE `map` NOT IN (SELECT `entry` FROM `worldmap_info`);
DELETE FROM `graveyards` WHERE `mapid` NOT IN (SELECT `entry` FROM `worldmap_info`);
DELETE FROM `itempages_localized` WHERE `entry` NOT IN (SELECT DISTINCT `entry` FROM `itempages`);
DELETE FROM `itempetfood` WHERE `entry` NOT IN (SELECT `entry` FROM `items`);
DELETE FROM `items_localized` WHERE `entry` NOT IN (SELECT DISTINCT `entry` FROM `items`);
DELETE FROM `item_quest_association` WHERE `item` NOT IN (SELECT `entry` FROM `items`);
DELETE FROM `item_quest_association` WHERE `quest` NOT IN (SELECT `entry` FROM `quests`);
DELETE FROM `items_localized` WHERE `entry` NOT IN (SELECT DISTINCT `entry` FROM `items`);
DELETE FROM `loot_creatures` WHERE `itemid` NOT IN (SELECT entry from `items`);
DELETE FROM `loot_fishing` WHERE `itemid` NOT IN (SELECT entry from `items`);
DELETE FROM `loot_gameobjects` WHERE `itemid` NOT IN (SELECT entry from `items`);
DELETE FROM `loot_items` WHERE `itemid` NOT IN (SELECT entry from `items`);
DELETE FROM `loot_pickpocketing` WHERE `itemid` NOT IN (SELECT entry from `items`);
DELETE FROM `loot_skinning` WHERE `itemid` NOT IN (SELECT entry from `items`);
DELETE FROM `loot_disenchanting` WHERE `itemid` NOT IN (SELECT entry from `items`);
DELETE FROM `loot_disenchanting` WHERE `entryid` NOT IN (SELECT `entry` FROM `items`);
DELETE FROM `loot_creatures` WHERE `entryid` NOT IN (SELECT entry from `creature_names`);
DELETE FROM `loot_pickpocketing` WHERE `entryid` NOT IN (SELECT entry from `creature_names`);
DELETE FROM `loot_skinning` WHERE `entryid` NOT IN (SELECT entry from `creature_names`);
DELETE FROM `loot_creatures` WHERE `entryid` NOT IN (SELECT entry from `creature_proto`);
DELETE FROM `loot_pickpocketing` WHERE `entryid` NOT IN (SELECT entry from `creature_proto`);
DELETE FROM `loot_skinning` WHERE `entryid` NOT IN (SELECT entry from `creature_proto`);
DELETE FROM `loot_gameobjects` WHERE `entryid` NOT IN (SELECT entry FROM `gameobject_names`);
DELETE FROM `loot_fishing` WHERE `entryid` NOT IN (SELECT `Zone` FROM `fishing`);
DELETE FROM `loot_gameobjects` WHERE `entryid` NOT IN (SELECT `entry` FROM `gameobject_names` WHERE `type` = 3);
DELETE FROM `loot_pickpocketing` WHERE `entryid` NOT IN (SELECT `entry` FROM `creature_names` WHERE `type` = 7);
DELETE FROM `loot_skinning` WHERE `entryid` IN (SELECT `entry` FROM `creature_names` WHERE `type` = 8);
DELETE FROM `npc_gossip_textid` WHERE `creatureid` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `npc_gossip_textid` WHERE `textid` NOT IN (SELECT `entry` FROM `npc_text`);
DELETE FROM `npc_monstersay` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `npc_text_localized` WHERE `entry` NOT IN (SELECT `entry` FROM `npc_text`);
DELETE FROM `petdefaultspells` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `playercreateinfo_items` WHERE `indexid` NOT IN (SELECT `index` FROM `playercreateinfo`);
DELETE FROM `playercreateinfo_skills` WHERE `indexid` NOT IN (SELECT `index` FROM `playercreateinfo`);
DELETE FROM `playercreateinfo_spells` WHERE `indexid` NOT IN (SELECT `index` FROM `playercreateinfo`);
DELETE FROM `quests_localized` WHERE `entry` NOT IN (SELECT `entry` FROM `quests`);
DELETE FROM `recall` WHERE `MapId` NOT IN (SELECT `entry` FROM `worldmap_info`);
DELETE FROM `reputation_creature_onkill` WHERE `creature_id` NOT IN (SELECT `entry` FROM `creature_proto`);
DELETE FROM `teleport_coords` WHERE `mapId` NOT IN (SELECT `entry` FROM `worldmap_info`);
DELETE FROM `trainer_defs` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `trainer_spells` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `vendors` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `vendors` WHERE `item` NOT IN (SELECT `entry` FROM `items`);
DELETE FROM `zoneguards` WHERE `horde_entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `zoneguards` WHERE `alliance_entry` NOT IN (SELECT `entry` FROM `creature_names`);
ALTER TABLE `ai_agents` ORDER BY `entry` ASC;
ALTER TABLE `ai_threattospellid` ORDER BY `spell` ASC;
ALTER TABLE `areatriggers` ORDER BY `entry` ASC;
ALTER TABLE `auctionhouse` ORDER BY `id` ASC;
ALTER TABLE `clientaddons` ORDER BY `id` ASC;
ALTER TABLE `creature_formations` ORDER BY `spawn_id` ASC;
ALTER TABLE `creature_names` ORDER BY `entry` ASC;
ALTER TABLE `creature_proto` ORDER BY `entry` ASC;
ALTER TABLE `creature_quest_finisher` ORDER BY `id` ASC;
ALTER TABLE `creature_quest_starter` ORDER BY `id` ASC;
ALTER TABLE `creature_spawns` ORDER BY `id` ASC;
ALTER TABLE `creature_waypoints` ORDER BY `spawnid` ASC;
ALTER TABLE `loot_creatures` ORDER BY `index` ASC;
ALTER TABLE `loot_disenchanting` ORDER BY `index` ASC;
ALTER TABLE `fishing` ORDER BY `Zone` ASC;
ALTER TABLE `loot_fishing` ORDER BY `index` ASC;
ALTER TABLE `gameobject_names` ORDER BY `entry` ASC;
ALTER TABLE `gameobject_quest_finisher` ORDER BY `id` ASC;
ALTER TABLE `gameobject_quest_item_binding` ORDER BY `entry` ASC;
ALTER TABLE `gameobject_quest_pickup_binding` ORDER BY `entry` ASC;
ALTER TABLE `gameobject_quest_starter` ORDER BY `id` ASC;
ALTER TABLE `gameobject_spawns` ORDER BY `id` ASC;
ALTER TABLE `graveyards` ORDER BY `id` ASC;
ALTER TABLE `item_quest_association` ORDER BY `item` ASC;
ALTER TABLE `item_randomprop_groups` ORDER BY `entry_id` ASC;
ALTER TABLE `item_randomsuffix_groups` ORDER BY `entry_id` ASC;
ALTER TABLE `loot_items` ORDER BY `index` ASC;
ALTER TABLE `itempages` ORDER BY `entry` ASC;
ALTER TABLE `itempetfood` ORDER BY `entry` ASC;
ALTER TABLE `items` ORDER BY `entry` ASC;
ALTER TABLE `npc_gossip_textid` ORDER BY `creatureid` ASC;
ALTER TABLE `npc_monstersay` ORDER BY `entry` ASC;
ALTER TABLE `npc_text` ORDER BY `entry` ASC;
ALTER TABLE `loot_gameobjects` ORDER BY `index` ASC;
ALTER TABLE `petdefaultspells` ORDER BY `entry` ASC;
ALTER TABLE `pet_information` ORDER BY `name` ASC;
ALTER TABLE `loot_pickpocketing` ORDER BY `index` ASC;
ALTER TABLE `playercreateinfo` ORDER BY `index` ASC;
ALTER TABLE `playercreateinfo_bars` ORDER BY `race` ASC;
ALTER TABLE `playercreateinfo_items` ORDER BY `indexid` ASC;
ALTER TABLE `playercreateinfo_skills` ORDER BY `indexid` ASC;
ALTER TABLE `playercreateinfo_spells` ORDER BY `indexid` ASC;
ALTER TABLE `quests` ORDER BY `entry` ASC;
ALTER TABLE `recall` ORDER BY `name` ASC;
ALTER TABLE `reputation_creature_onkill` ORDER BY `creature_id` ASC;
ALTER TABLE `reputation_faction_onkill` ORDER BY `faction_id` ASC;
ALTER TABLE `reputation_instance_onkill` ORDER BY `mapid` ASC;
ALTER TABLE `loot_skinning` ORDER BY `index` ASC;
ALTER TABLE `spell_coef_override` ORDER BY `id` ASC;
ALTER TABLE `spell_disable` ORDER BY `spellid` ASC;
ALTER TABLE `spell_disable_trainers` ORDER BY `spellid` ASC;
ALTER TABLE `spell_proc` ORDER BY `spellid` ASC;
ALTER TABLE `spellfixes` ORDER BY `spellid` ASC;
ALTER TABLE `spelloverride` ORDER BY `overrideid` ASC;
ALTER TABLE `teleport_coords` ORDER BY `id` ASC;
ALTER TABLE `totemspells` ORDER BY `spell` ASC;
ALTER TABLE `trainer_defs` ORDER BY `entry` ASC;
ALTER TABLE `trainer_spells` ORDER BY `entry` ASC;
ALTER TABLE `transport_data` ORDER BY `entry` ASC;
ALTER TABLE `vendors` ORDER BY `entry` ASC;
ALTER TABLE `version` ORDER BY `revision` ASC;
ALTER TABLE `weather` ORDER BY `zoneId` ASC;
ALTER TABLE `worldmap_info` ORDER BY `entry` ASC;
ALTER TABLE `zoneguards` ORDER BY `zone` ASC;
OPTIMIZE TABLE ai_agents,ai_threattospellid,areatriggers,auctionh ouse,banned_phrases,clientaddons,command_overrides ,creature_formations,creature_names,creature_names _localized,creature_proto,creature_quest_finisher, creature_quest_starter,creature_spawns,creature_st aticspawns,creature_timed_emotes,creature_waypoint s,fishing,gameobject_names,gameobject_names_locali zed,gameobject_quest_finisher,gameobject_quest_ite m_binding,gameobject_quest_pickup_binding,gameobje ct_quest_starter,gameobject_spawns,graveyards,item pages,itempages_localized,itempetfood,items,items_ localized,item_quest_association,item_randomprop_g roups,item_randomsuffix_groups,loot_creatures,loot _disenchanting,loot_fishing,loot_gameobjects,loot_ items,loot_pickpocketing,loot_skinning,map_checkpo int,npc_gossip_textid,npc_monstersay,npc_text,npc_ text_localized,petdefaultspells,pet_information,pl ayercreateinfo,playercreateinfo_bars,playercreatei nfo_items,playercreateinfo_skills,playercreateinfo _spells,quests,
quests_localized,recall,reputation_creature_onkill ,reputation_faction_onkill,reputation_instance_onk ill,spellcustoms,spellextra,spellfixes,spelloverri de,
spell_coef_override,spell_disable,spell_disable_tr ainers,spell_effects_override,spell_forced_targets ,spell_proc,spell_unique_auras,teleport_coords,tot emspells,trainer_defs,trainer_spells,transport_dat a,vendors,version,weather,wordfilter_character_nam es,wordfilter_chat,worldmap_info,worldstate_templa te,zoneguards;
REPAIR TABLE ai_agents,ai_threattospellid,areatriggers,auctionh ouse,banned_phrases,clientaddons,command_overrides ,creature_formations,creature_names,creature_names _localized,creature_proto,creature_quest_finisher, creature_quest_starter,creature_spawns,creature_st aticspawns,creature_timed_emotes,creature_waypoint s,fishing,gameobject_names,gameobject_names_locali zed,gameobject_quest_finisher,gameobject_quest_ite m_binding,gameobject_quest_pickup_binding,gameobje ct_quest_starter,gameobject_spawns,graveyards,item pages,itempages_localized,itempetfood,items,items_ localized,item_quest_association,item_randomprop_g roups,item_randomsuffix_groups,loot_creatures,loot _disenchanting,loot_fishing,loot_gameobjects,loot_ items,loot_pickpocketing,loot_skinning,map_checkpo int,npc_gossip_textid,npc_monstersay,npc_text,npc_ text_localized,petdefaultspells,pet_information,pl ayercreateinfo,playercreateinfo_bars,playercreatei nfo_items,playercreateinfo_skills,playercreateinfo _spells,quests,
quests_localized,recall,reputation_creature_onkill ,reputation_faction_onkill,reputation_instance_onk ill,spellcustoms,spellextra,spellfixes,spelloverri de,
spell_coef_override,spell_disable,spell_disable_tr ainers,spell_effects_override,spell_forced_targets ,spell_proc,spell_unique_auras,teleport_coords,tot emspells,trainer_defs,trainer_spells,transport_dat a,vendors,version,weather,wordfilter_character_nam es,wordfilter_chat,worldmap_info,worldstate_templa te,zoneguards;
Credits: technique, Walla
UPDATE `creature_spawns` SET `flags` = '768', `bytes1` = '16843008', `bytes2` = '1' WHERE `entry` IN (SELECT `entry` FROM `creature_names` WHERE `name` LIKE '%Alliance Spirit Guide%' OR `name` LIKE '%Horde Spirit Guide%' OR `name` LIKE '%Taunka Spirit Guide%' OR `name` LIKE '%Dwarven Spirit Guide%');
UPDATE `creature_spawns` SET `flags` = '768', `bytes1` = '16843008', `bytes2` = '1' WHERE `entry` = '6491';
UPDATE `creature_proto` SET `walk_speed` = 2.5, `run_speed` = 8, `fly_speed` = '14';
UPDATE `creature_names` SET `flags1` = `flags1`|'108' WHERE `rank` = '3';
UPDATE `creature_waypoints` SET `forwardskinid` = (SELECT `displayid` FROM `creature_spawns` where `id` = `spawnid`);
UPDATE `creature_waypoints` SET `backwardskinid` = (SELECT `displayid` FROM `creature_spawns` where `id` = `spawnid`);
UPDATE `creature_spawns` SET `movetype` = '0' WHERE `id` NOT IN (SELECT `spawnid` FROM `creature_waypoints`);
ALTER TABLE `creature_spawns` AUTO_INCREMENT=1, ADD COLUMN `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, CHANGE COLUMN `id` `id2` INT(11) UNSIGNED NOT NULL AFTER `id`, DROP PRIMARY KEY, ADD PRIMARY KEY (`id`);
ALTER TABLE `creature_spawns` ADD INDEX `id2` (`id2`);
UPDATE `creature_waypoints`,creature_spawns SET `spawnid` = id where spawnid = id2;
UPDATE `creature_formations`,creature_spawns SET `spawn_id` = id where `spawn_id` = id2;
UPDATE `creature_formations`,creature_spawns SET `target_spawn_id` = id where `target_spawn_id` = id2;
ALTER TABLE `creature_spawns` DROP COLUMN `id2`;
ALTER TABLE `gameobject_spawns` AUTO_INCREMENT=1, ADD COLUMN `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, CHANGE COLUMN `id` `id2` INT(11) UNSIGNED NOT NULL AFTER `id`, DROP PRIMARY KEY, ADD PRIMARY KEY (`id`);
ALTER TABLE `gameobject_spawns` ADD INDEX `id2` (`id2`);
ALTER TABLE `gameobject_spawns` DROP COLUMN `id2`;
ALTER TABLE `loot_creatures` AUTO_INCREMENT=1, ADD COLUMN `index` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, CHANGE COLUMN `index` `index2` INT(11) UNSIGNED NOT NULL AFTER `index`, DROP PRIMARY KEY, ADD PRIMARY KEY (`index`);
ALTER TABLE `loot_creatures` ADD INDEX `index2` (`index2`);
ALTER TABLE `loot_creatures` DROP COLUMN `index2`;
ALTER TABLE `loot_disenchanting` AUTO_INCREMENT=1, ADD COLUMN `index` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, CHANGE COLUMN `index` `index2` INT(11) UNSIGNED NOT NULL AFTER `index`, DROP PRIMARY KEY, ADD PRIMARY KEY (`index`);
ALTER TABLE `loot_disenchanting` ADD INDEX `index2` (`index2`);
ALTER TABLE `loot_disenchanting` DROP COLUMN `index2`;
ALTER TABLE `loot_fishing` AUTO_INCREMENT=1, ADD COLUMN `index` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, CHANGE COLUMN `index` `index2` INT(11) UNSIGNED NOT NULL AFTER `index`, DROP PRIMARY KEY, ADD PRIMARY KEY (`index`);
ALTER TABLE `loot_fishing` ADD INDEX `index2` (`index2`);
ALTER TABLE `loot_fishing` DROP COLUMN `index2`;
ALTER TABLE `loot_gameobjects` AUTO_INCREMENT=1, ADD COLUMN `index` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, CHANGE COLUMN `index` `index2` INT(11) UNSIGNED NOT NULL AFTER `index`, DROP PRIMARY KEY, ADD PRIMARY KEY (`index`);
ALTER TABLE `loot_gameobjects` ADD INDEX `index2` (`index2`);
ALTER TABLE `loot_gameobjects` DROP COLUMN `index2`;
ALTER TABLE `loot_items` AUTO_INCREMENT=1, ADD COLUMN `index` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, CHANGE COLUMN `index` `index2` INT(11) UNSIGNED NOT NULL AFTER `index`, DROP PRIMARY KEY, ADD PRIMARY KEY (`index`);
ALTER TABLE `loot_items` ADD INDEX `index2` (`index2`);
ALTER TABLE `loot_items` DROP COLUMN `index2`;
ALTER TABLE `loot_pickpocketing` AUTO_INCREMENT=1, ADD COLUMN `index` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, CHANGE COLUMN `index` `index2` INT(11) UNSIGNED NOT NULL AFTER `index`, DROP PRIMARY KEY, ADD PRIMARY KEY (`index`);
ALTER TABLE `loot_pickpocketing` ADD INDEX `index2` (`index2`);
ALTER TABLE `loot_pickpocketing` DROP COLUMN `index2`;
ALTER TABLE `loot_skinning` AUTO_INCREMENT=1, ADD COLUMN `index` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, CHANGE COLUMN `index` `index2` INT(11) UNSIGNED NOT NULL AFTER `index`, DROP PRIMARY KEY, ADD PRIMARY KEY (`index`);
ALTER TABLE `loot_skinning` ADD INDEX `index2` (`index2`);
ALTER TABLE `loot_skinning` DROP COLUMN `index2`;
DELETE FROM `ai_agents` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `creature_formations` WHERE `spawn_id` NOT IN (SELECT `id` FROM `creature_spawns`);
DELETE FROM `creature_formations` WHERE `target_spawn_id` NOT IN (SELECT `id` FROM `creature_spawns`);
DELETE FROM `creature_names_localized` WHERE `id` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `creature_proto` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `creature_quest_finisher` WHERE `id` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `creature_quest_finisher` WHERE `quest` NOT IN (SELECT `quest` FROM `quests`);
DELETE FROM `creature_quest_starter` WHERE `id` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `creature_quest_starter` WHERE `quest` NOT IN (SELECT `quest` FROM `quests`);
DELETE FROM `creature_spawns` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `creature_spawns` WHERE `map` NOT IN (SELECT `entry` FROM `worldmap_info`);
DELETE FROM `creature_staticspawns` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `creature_staticspawns` WHERE `map` NOT IN (SELECT `entry` FROM `worldmap_info`);
DELETE FROM `creature_timed_emotes` WHERE `spawnid` NOT IN (SELECT `id` FROM `creature_spawns`);
DELETE FROM `creature_waypoints` WHERE `spawnid` NOT IN (SELECT `id` FROM `creature_spawns`);
DELETE FROM `gameobject_names_localized` WHERE `entry` NOT IN (SELECT `entry` FROM `gameobject_names`);
DELETE FROM `gameobject_quest_finisher` WHERE `id` NOT IN (SELECT `entry` FROM `gameobject_names`);
DELETE FROM `gameobject_quest_finisher` WHERE `quest` NOT IN (SELECT `quest` FROM `quests`);
DELETE FROM `gameobject_quest_item_binding` WHERE `entry` NOT IN (SELECT `entry` FROM `gameobject_names`);
DELETE FROM `gameobject_quest_item_binding` WHERE `quest` NOT IN (SELECT `quest` FROM `quests`);
DELETE FROM `gameobject_quest_item_binding` WHERE `item` NOT IN (SELECT `entry` FROM `items`);
DELETE FROM `gameobject_quest_pickup_binding` WHERE `entry` NOT IN (SELECT `entry` FROM `gameobject_names`);
DELETE FROM `gameobject_quest_pickup_binding` WHERE `quest` NOT IN (SELECT `quest` FROM `quests`);
DELETE FROM `gameobject_quest_starter` WHERE `id` NOT IN (SELECT `entry` FROM `gameobject_names`);
DELETE FROM `gameobject_quest_starter` WHERE `quest` NOT IN (SELECT `quest` FROM `quests`);
DELETE FROM `gameobject_spawns` WHERE `entry` NOT IN (SELECT `entry` FROM `gameobject_names`);
DELETE FROM `gameobject_spawns` WHERE `map` NOT IN (SELECT `entry` FROM `worldmap_info`);
DELETE FROM `graveyards` WHERE `mapid` NOT IN (SELECT `entry` FROM `worldmap_info`);
DELETE FROM `itempages_localized` WHERE `entry` NOT IN (SELECT DISTINCT `entry` FROM `itempages`);
DELETE FROM `itempetfood` WHERE `entry` NOT IN (SELECT `entry` FROM `items`);
DELETE FROM `items_localized` WHERE `entry` NOT IN (SELECT DISTINCT `entry` FROM `items`);
DELETE FROM `item_quest_association` WHERE `item` NOT IN (SELECT `entry` FROM `items`);
DELETE FROM `item_quest_association` WHERE `quest` NOT IN (SELECT `entry` FROM `quests`);
DELETE FROM `items_localized` WHERE `entry` NOT IN (SELECT DISTINCT `entry` FROM `items`);
DELETE FROM `loot_creatures` WHERE `itemid` NOT IN (SELECT entry from `items`);
DELETE FROM `loot_fishing` WHERE `itemid` NOT IN (SELECT entry from `items`);
DELETE FROM `loot_gameobjects` WHERE `itemid` NOT IN (SELECT entry from `items`);
DELETE FROM `loot_items` WHERE `itemid` NOT IN (SELECT entry from `items`);
DELETE FROM `loot_pickpocketing` WHERE `itemid` NOT IN (SELECT entry from `items`);
DELETE FROM `loot_skinning` WHERE `itemid` NOT IN (SELECT entry from `items`);
DELETE FROM `loot_disenchanting` WHERE `itemid` NOT IN (SELECT entry from `items`);
DELETE FROM `loot_disenchanting` WHERE `entryid` NOT IN (SELECT `entry` FROM `items`);
DELETE FROM `loot_creatures` WHERE `entryid` NOT IN (SELECT entry from `creature_names`);
DELETE FROM `loot_pickpocketing` WHERE `entryid` NOT IN (SELECT entry from `creature_names`);
DELETE FROM `loot_skinning` WHERE `entryid` NOT IN (SELECT entry from `creature_names`);
DELETE FROM `loot_creatures` WHERE `entryid` NOT IN (SELECT entry from `creature_proto`);
DELETE FROM `loot_pickpocketing` WHERE `entryid` NOT IN (SELECT entry from `creature_proto`);
DELETE FROM `loot_skinning` WHERE `entryid` NOT IN (SELECT entry from `creature_proto`);
DELETE FROM `loot_gameobjects` WHERE `entryid` NOT IN (SELECT entry FROM `gameobject_names`);
DELETE FROM `loot_fishing` WHERE `entryid` NOT IN (SELECT `Zone` FROM `fishing`);
DELETE FROM `loot_gameobjects` WHERE `entryid` NOT IN (SELECT `entry` FROM `gameobject_names` WHERE `type` = 3);
DELETE FROM `loot_pickpocketing` WHERE `entryid` NOT IN (SELECT `entry` FROM `creature_names` WHERE `type` = 7);
DELETE FROM `loot_skinning` WHERE `entryid` IN (SELECT `entry` FROM `creature_names` WHERE `type` = 8);
DELETE FROM `npc_gossip_textid` WHERE `creatureid` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `npc_gossip_textid` WHERE `textid` NOT IN (SELECT `entry` FROM `npc_text`);
DELETE FROM `npc_monstersay` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `npc_text_localized` WHERE `entry` NOT IN (SELECT `entry` FROM `npc_text`);
DELETE FROM `petdefaultspells` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `playercreateinfo_items` WHERE `indexid` NOT IN (SELECT `index` FROM `playercreateinfo`);
DELETE FROM `playercreateinfo_skills` WHERE `indexid` NOT IN (SELECT `index` FROM `playercreateinfo`);
DELETE FROM `playercreateinfo_spells` WHERE `indexid` NOT IN (SELECT `index` FROM `playercreateinfo`);
DELETE FROM `quests_localized` WHERE `entry` NOT IN (SELECT `entry` FROM `quests`);
DELETE FROM `recall` WHERE `MapId` NOT IN (SELECT `entry` FROM `worldmap_info`);
DELETE FROM `reputation_creature_onkill` WHERE `creature_id` NOT IN (SELECT `entry` FROM `creature_proto`);
DELETE FROM `teleport_coords` WHERE `mapId` NOT IN (SELECT `entry` FROM `worldmap_info`);
DELETE FROM `trainer_defs` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `trainer_spells` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `vendors` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `vendors` WHERE `item` NOT IN (SELECT `entry` FROM `items`);
DELETE FROM `zoneguards` WHERE `horde_entry` NOT IN (SELECT `entry` FROM `creature_names`);
DELETE FROM `zoneguards` WHERE `alliance_entry` NOT IN (SELECT `entry` FROM `creature_names`);
ALTER TABLE `ai_agents` ORDER BY `entry` ASC;
ALTER TABLE `ai_threattospellid` ORDER BY `spell` ASC;
ALTER TABLE `areatriggers` ORDER BY `entry` ASC;
ALTER TABLE `auctionhouse` ORDER BY `id` ASC;
ALTER TABLE `clientaddons` ORDER BY `id` ASC;
ALTER TABLE `creature_formations` ORDER BY `spawn_id` ASC;
ALTER TABLE `creature_names` ORDER BY `entry` ASC;
ALTER TABLE `creature_proto` ORDER BY `entry` ASC;
ALTER TABLE `creature_quest_finisher` ORDER BY `id` ASC;
ALTER TABLE `creature_quest_starter` ORDER BY `id` ASC;
ALTER TABLE `creature_spawns` ORDER BY `id` ASC;
ALTER TABLE `creature_waypoints` ORDER BY `spawnid` ASC;
ALTER TABLE `loot_creatures` ORDER BY `index` ASC;
ALTER TABLE `loot_disenchanting` ORDER BY `index` ASC;
ALTER TABLE `fishing` ORDER BY `Zone` ASC;
ALTER TABLE `loot_fishing` ORDER BY `index` ASC;
ALTER TABLE `gameobject_names` ORDER BY `entry` ASC;
ALTER TABLE `gameobject_quest_finisher` ORDER BY `id` ASC;
ALTER TABLE `gameobject_quest_item_binding` ORDER BY `entry` ASC;
ALTER TABLE `gameobject_quest_pickup_binding` ORDER BY `entry` ASC;
ALTER TABLE `gameobject_quest_starter` ORDER BY `id` ASC;
ALTER TABLE `gameobject_spawns` ORDER BY `id` ASC;
ALTER TABLE `graveyards` ORDER BY `id` ASC;
ALTER TABLE `item_quest_association` ORDER BY `item` ASC;
ALTER TABLE `item_randomprop_groups` ORDER BY `entry_id` ASC;
ALTER TABLE `item_randomsuffix_groups` ORDER BY `entry_id` ASC;
ALTER TABLE `loot_items` ORDER BY `index` ASC;
ALTER TABLE `itempages` ORDER BY `entry` ASC;
ALTER TABLE `itempetfood` ORDER BY `entry` ASC;
ALTER TABLE `items` ORDER BY `entry` ASC;
ALTER TABLE `npc_gossip_textid` ORDER BY `creatureid` ASC;
ALTER TABLE `npc_monstersay` ORDER BY `entry` ASC;
ALTER TABLE `npc_text` ORDER BY `entry` ASC;
ALTER TABLE `loot_gameobjects` ORDER BY `index` ASC;
ALTER TABLE `petdefaultspells` ORDER BY `entry` ASC;
ALTER TABLE `pet_information` ORDER BY `name` ASC;
ALTER TABLE `loot_pickpocketing` ORDER BY `index` ASC;
ALTER TABLE `playercreateinfo` ORDER BY `index` ASC;
ALTER TABLE `playercreateinfo_bars` ORDER BY `race` ASC;
ALTER TABLE `playercreateinfo_items` ORDER BY `indexid` ASC;
ALTER TABLE `playercreateinfo_skills` ORDER BY `indexid` ASC;
ALTER TABLE `playercreateinfo_spells` ORDER BY `indexid` ASC;
ALTER TABLE `quests` ORDER BY `entry` ASC;
ALTER TABLE `recall` ORDER BY `name` ASC;
ALTER TABLE `reputation_creature_onkill` ORDER BY `creature_id` ASC;
ALTER TABLE `reputation_faction_onkill` ORDER BY `faction_id` ASC;
ALTER TABLE `reputation_instance_onkill` ORDER BY `mapid` ASC;
ALTER TABLE `loot_skinning` ORDER BY `index` ASC;
ALTER TABLE `spell_coef_override` ORDER BY `id` ASC;
ALTER TABLE `spell_disable` ORDER BY `spellid` ASC;
ALTER TABLE `spell_disable_trainers` ORDER BY `spellid` ASC;
ALTER TABLE `spell_proc` ORDER BY `spellid` ASC;
ALTER TABLE `spellfixes` ORDER BY `spellid` ASC;
ALTER TABLE `spelloverride` ORDER BY `overrideid` ASC;
ALTER TABLE `teleport_coords` ORDER BY `id` ASC;
ALTER TABLE `totemspells` ORDER BY `spell` ASC;
ALTER TABLE `trainer_defs` ORDER BY `entry` ASC;
ALTER TABLE `trainer_spells` ORDER BY `entry` ASC;
ALTER TABLE `transport_data` ORDER BY `entry` ASC;
ALTER TABLE `vendors` ORDER BY `entry` ASC;
ALTER TABLE `version` ORDER BY `revision` ASC;
ALTER TABLE `weather` ORDER BY `zoneId` ASC;
ALTER TABLE `worldmap_info` ORDER BY `entry` ASC;
ALTER TABLE `zoneguards` ORDER BY `zone` ASC;
OPTIMIZE TABLE ai_agents,ai_threattospellid,areatriggers,auctionh ouse,banned_phrases,clientaddons,command_overrides ,creature_formations,creature_names,creature_names _localized,creature_proto,creature_quest_finisher, creature_quest_starter,creature_spawns,creature_st aticspawns,creature_timed_emotes,creature_waypoint s,fishing,gameobject_names,gameobject_names_locali zed,gameobject_quest_finisher,gameobject_quest_ite m_binding,gameobject_quest_pickup_binding,gameobje ct_quest_starter,gameobject_spawns,graveyards,item pages,itempages_localized,itempetfood,items,items_ localized,item_quest_association,item_randomprop_g roups,item_randomsuffix_groups,loot_creatures,loot _disenchanting,loot_fishing,loot_gameobjects,loot_ items,loot_pickpocketing,loot_skinning,map_checkpo int,npc_gossip_textid,npc_monstersay,npc_text,npc_ text_localized,petdefaultspells,pet_information,pl ayercreateinfo,playercreateinfo_bars,playercreatei nfo_items,playercreateinfo_skills,playercreateinfo _spells,quests,
quests_localized,recall,reputation_creature_onkill ,reputation_faction_onkill,reputation_instance_onk ill,spellcustoms,spellextra,spellfixes,spelloverri de,
spell_coef_override,spell_disable,spell_disable_tr ainers,spell_effects_override,spell_forced_targets ,spell_proc,spell_unique_auras,teleport_coords,tot emspells,trainer_defs,trainer_spells,transport_dat a,vendors,version,weather,wordfilter_character_nam es,wordfilter_chat,worldmap_info,worldstate_templa te,zoneguards;
REPAIR TABLE ai_agents,ai_threattospellid,areatriggers,auctionh ouse,banned_phrases,clientaddons,command_overrides ,creature_formations,creature_names,creature_names _localized,creature_proto,creature_quest_finisher, creature_quest_starter,creature_spawns,creature_st aticspawns,creature_timed_emotes,creature_waypoint s,fishing,gameobject_names,gameobject_names_locali zed,gameobject_quest_finisher,gameobject_quest_ite m_binding,gameobject_quest_pickup_binding,gameobje ct_quest_starter,gameobject_spawns,graveyards,item pages,itempages_localized,itempetfood,items,items_ localized,item_quest_association,item_randomprop_g roups,item_randomsuffix_groups,loot_creatures,loot _disenchanting,loot_fishing,loot_gameobjects,loot_ items,loot_pickpocketing,loot_skinning,map_checkpo int,npc_gossip_textid,npc_monstersay,npc_text,npc_ text_localized,petdefaultspells,pet_information,pl ayercreateinfo,playercreateinfo_bars,playercreatei nfo_items,playercreateinfo_skills,playercreateinfo _spells,quests,
quests_localized,recall,reputation_creature_onkill ,reputation_faction_onkill,reputation_instance_onk ill,spellcustoms,spellextra,spellfixes,spelloverri de,
spell_coef_override,spell_disable,spell_disable_tr ainers,spell_effects_override,spell_forced_targets ,spell_proc,spell_unique_auras,teleport_coords,tot emspells,trainer_defs,trainer_spells,transport_dat a,vendors,version,weather,wordfilter_character_nam es,wordfilter_chat,worldmap_info,worldstate_templa te,zoneguards;