[SQL World Cleanup]

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

Code:

---

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,auctionhouse,banned_phrases,clientaddons,command_overrides,creature_formations,creature_names,creature_names_localized,creature_proto,creature_quest_finisher,creature_quest_starter,creature_spawns,creature_staticspawns,creature_timed_emotes,creature_waypoints,fishing,gameobject_names,gameobject_names_localized,gameobject_quest_finisher,gameobject_quest_item_binding,gameobject_quest_pickup_binding,gameobject_quest_starter,gameobject_spawns,graveyards,itempages,itempages_localized,itempetfood,items,items_localized,item_quest_association,item_randomprop_groups,item_randomsuffix_groups,loot_creatures,loot_disenchanting,loot_fishing,loot_gameobjects,loot_items,loot_pickpocketing,loot_skinning,map_checkpoint,npc_gossip_textid,npc_monstersay,npc_text,npc_text_localized,petdefaultspells,pet_information,playercreateinfo,playercreateinfo_bars,playercreateinfo_items,playercreateinfo_skills,playercreateinfo_spells,quests,
quests_localized,recall,reputation_creature_onkill,reputation_faction_onkill,reputation_instance_onkill,spellcustoms,spellextra,spellfixes,spelloverride,
spell_coef_override,spell_disable,spell_disable_trainers,spell_effects_override,spell_forced_targets,spell_proc,spell_unique_auras,teleport_coords,totemspells,trainer_defs,trainer_spells,transport_data,vendors,version,weather,wordfilter_character_names,wordfilter_chat,worldmap_info,worldstate_template,zoneguards;

REPAIR TABLE ai_agents,ai_threattospellid,areatriggers,auctionhouse,banned_phrases,clientaddons,command_overrides,creature_formations,creature_names,creature_names_localized,creature_proto,creature_quest_finisher,creature_quest_starter,creature_spawns,creature_staticspawns,creature_timed_emotes,creature_waypoints,fishing,gameobject_names,gameobject_names_localized,gameobject_quest_finisher,gameobject_quest_item_binding,gameobject_quest_pickup_binding,gameobject_quest_starter,gameobject_spawns,graveyards,itempages,itempages_localized,itempetfood,items,items_localized,item_quest_association,item_randomprop_groups,item_randomsuffix_groups,loot_creatures,loot_disenchanting,loot_fishing,loot_gameobjects,loot_items,loot_pickpocketing,loot_skinning,map_checkpoint,npc_gossip_textid,npc_monstersay,npc_text,npc_text_localized,petdefaultspells,pet_information,playercreateinfo,playercreateinfo_bars,playercreateinfo_items,playercreateinfo_skills,playercreateinfo_spells,quests,
quests_localized,recall,reputation_creature_onkill,reputation_faction_onkill,reputation_instance_onkill,spellcustoms,spellextra,spellfixes,spelloverride,
spell_coef_override,spell_disable,spell_disable_trainers,spell_effects_override,spell_forced_targets,spell_proc,spell_unique_auras,teleport_coords,totemspells,trainer_defs,trainer_spells,transport_data,vendors,version,weather,wordfilter_character_names,wordfilter_chat,worldmap_info,worldstate_template,zoneguards;

---