[Release] Usefull Database Queries

[Illidan1]

Nice man, Thanks

[latruwski]

Nice man, Thanks

remember if you have ideas for a query, i'll see if i can add

grtz

[latruwski]

updated once again

grtz

[latruwski]

Updated egain with a query to remove all item requirements...

i think i am almost out of creativity ^^ anyone else got an idea?

grtz

[knaur]

nice nice nice =) +2 rep from me

[secretdragon008]

very good,it was very very much i didt know of there.
ty and +Rep

[mafiaboy]

+rep, this should be stickied

[latruwski]

+rep, this should be stickied

well i don't think it will be... i made it a while ago now.. so if it was that good they would have stickied already i think ^^
anyhow thx for the great comments

i am thinking of new queries... if you can think of anything that can be done with queries or that you think that can be done by queries post here... cuz i am running out of ideas


grtz

[cynicalistik]

found a few useful too me, thanx

[latruwski]

found a few useful too me, thanx

no problem

[latruwski]

Maintenance Queries thx to NCDB:

-- Delete vendors with none existing creatures/items.
DELETE FROM `vendors` WHERE `entry` not in (SELECT `entry` from `creature_proto`);
DELETE FROM `vendors` WHERE `item` not in (SELECT `entry` from `items`);

-- Delete waypoints with none existing spawns
DELETE FROM `creature_waypoints` WHERE `spawnid` not in (SELECT `id` from `creature_spawns`);

-- Delete trainers with none existing creatures ** New table **
DELETE FROM `trainer_spells` WHERE `entry` not in (SELECT `entry` from `creature_proto`);
DELETE FROM `trainer_defs` WHERE `entry` not in (SELECT `entry` from `creature_proto`);

-- Delete quest starter/finisher where quests doesn't exist
DELETE FROM `creature_quest_starter` WHERE `quest` not in (SELECT `entry` from `quests`);
DELETE FROM `creature_quest_finisher` WHERE `quest` not in (SELECT `entry` from `quests`);
DELETE FROM `gameobject_quest_starter` WHERE `quest` not in (SELECT `entry` from `quests`);
DELETE FROM `gameobject_quest_finisher` WHERE `quest` not in (SELECT `entry` from `quests`);

-- Delete quest starter/finisher where creature/gameobject doesn't exist
DELETE FROM `creature_quest_starter` WHERE `id` not in (SELECT `entry` from `creature_proto`);
DELETE FROM `creature_quest_finisher` WHERE `id` not in (SELECT `entry` from `creature_proto`);
DELETE FROM `gameobject_quest_starter` WHERE `id` not in (SELECT `entry` from `gameobject_names`);
DELETE FROM `gameobject_quest_finisher` WHERE `id` not in (SELECT `entry` from `gameobject_names`);

-- Delete loots that itemid not exists.
DELETE FROM `itemloot` WHERE `itemid` not in (SELECT `entry` from `items`);
DELETE FROM `objectloot` WHERE `itemid` not in (SELECT `entry` from `items`);
DELETE FROM `creatureloot` WHERE `itemid` not in (SELECT `entry` from `items`);
DELETE FROM `pickpocketingloot` WHERE `itemid` not in (SELECT `entry` from `items`);
DELETE FROM `fishingloot` WHERE `itemid` not in (SELECT `entry` from `items`);
DELETE FROM `skinningloot` WHERE `itemid` not in (SELECT `entry` from `items`);
DELETE FROM `prospectingloot` WHERE `itemid` not in (SELECT `entry` from `items`);

-- Delete loots that entryid not exists.
DELETE FROM `itemloot` WHERE `entryid` not in (SELECT `entry` from `items`);
DELETE FROM `prospectingloot` WHERE `entryid` not in (SELECT `entry` from `items`);
DELETE FROM `fishingloot` WHERE `entryid` not in (SELECT `Zone` from `fishing`);
DELETE FROM `objectloot` WHERE `entryid` not in (SELECT `entry` from `gameobject_names`);
DELETE FROM `creatureloot` WHERE `entryid` not in (SELECT `entry` from `creature_proto`);
DELETE FROM `pickpocketingloot` WHERE `entryid` not in (SELECT `entry` from `creature_proto`);
DELETE FROM `skinningloot` WHERE `entryid` not in (SELECT `entry` from `creature_proto`);

Query to order you tables again thx to NCDB:

ALTER TABLE `areatriggers` ORDER BY `entry` ASC;
ALTER TABLE `creature_spawns` ORDER BY `id` ASC;
ALTER TABLE `creature_quest_starter` ORDER BY `id` ASC, `quest` ASC;
ALTER TABLE `creature_proto` ORDER BY `entry` ASC;
ALTER TABLE `creature_names` ORDER BY `entry` ASC;
ALTER TABLE `gameobject_names` ORDER BY `entry` ASC;
ALTER TABLE `gameobject_quest_finisher` ORDER BY `id` ASC, `quest` ASC;
ALTER TABLE `gameobject_quest_starter` ORDER BY `id` ASC, `quest` ASC;
ALTER TABLE `gameobject_spawns` ORDER BY `id` ASC;
ALTER TABLE `itempages` ORDER BY `entry` ASC;
ALTER TABLE `items` ORDER BY `entry` ASC;
ALTER TABLE `npc_text` ORDER BY `entry` 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 `vendors` ORDER BY `vendorGUID` ASC, `entry` ASC;
ALTER TABLE `weather` ORDER BY `zoneId` ASC;

these are verry nice too forgot to add them and now i cant add to first post, it is too big xD

grtz

[latruwski]

in future i will add queries in a new reply.. (i cant add more to the first post ) maybe mods will be able to move the reply to the first page... i will ask..

grtz

[Succy]

These are really, really nice! Should be a sticky

[latruwski]

These are really, really nice! Should be a sticky

ty, i thought this post was doomed to die on the last page of emu discussion anyhow i made a new thread with part 2 of the queries, link is in the first post

grtz