Useful SQL Queries

tok124

Trial Member
5
2021
2
Hello ! Today i will share some useful SQL queries i wrote a while ago. I think it can be useful to others here so i decided to share them here so if you do find these queries useful let me know. And i will be releasing more useful queries in the future :)

This query can be used to inspect a players inventory (It will also show the items that the player has equipped) Just replace PlayerName. This query must run in Characters Database
SELECT ii.itemEntry, it.name AS ItemName, ch.name AS CharName, COUNT(*) AS amount, CASE WHEN ii.`count` > 1 THEN ii.`count` ELSE NULL END as stackAmount FROM item_instance ii
INNER JOIN world.item_template it
ON ii.itemEntry = it.entry
INNER JOIN characters ch
ON ch.guid = ii.owner_guid
WHERE ch.name = "PlayerName" GROUP BY ii.itemEntry, it.name, ch.name;

This query can be used to add an Item Set to a vendor (Run in World Database)
SET
@npcid = 12345, -- npc id here
@setid = 123; -- itemset id here

INSERT INTO npc_vendor(entry, item)
SELECT @npcid, entry FROM item_template WHERE itemset = @setid;

This query will replace a part of the name with something else. For example, lets say you have item "Wrathful Gladiator's Treads of Dominance" and you want to replace "Wrathful" with "NewName" this query will do the job for you. Keep in mind that this will target ALL wrathful items and replace wrathful with NewName (Run in World Database)
UPDATE item_template
SET name = REPLACE(name, "Wrathful", "NewName"), Quality = 5
WHERE name LIKE "Wrathful Gladiator's%";

This is how you can make a loop it SQL. It works very much like a for loop in any programming language. in this case i have used it for inserting new rows to player_levelstats
DROP PROCEDURE IF EXISTS `levelloop`;
DELIMITER $$
CREATE PROCEDURE `levelloop`()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 255 DO
INSERT INTO player_levelstats (race, class, `level`, str, agi, sta, inte, spi) VALUES (1, 1, i, i, i, i, i, i);
SET i = i + 1;
END WHILE;
END $$
DELIMITER ;
CALL `levelloop`;
DROP PROCEDURE IF EXISTS `levelloop`;

This query is Outdated since sha_pass_hash does not exists anymore in account table but if you have not updated your core and still have the sha_pass_hash column this query will work for you to create an account directly in the database (Run in Auth Database)
SET
@username = "tok124",
@pass = "123456";

INSERT INTO account (username, sha_pass_hash)
VALUES (@username, SHA1(CONCAT(UPPER(@username), ":", UPPER(@pass))));

This query can be used to calculate dmg_min1 and dmg_max1 by dps. So lets say that you want your weapon to have exactly 1000 DPS this query will calculate the dmg_min1 and dmg_max1 to give your weapon exactly 1000 DPS (Run in World Database)
SET
@entry = 60002,
@dps = 10000;

UPDATE item_template
SET
dmg_min1 = ROUND((@dps*delay/1000) - ((200 + 400)/2)),
dmg_max1 = ROUND((@dps*delay/1000) + ((200 + 400)/2))
WHERE entry = @entry;

This query can be used to calculate the correct health, mana, armor and damage for your creatures (This query requires unit_class 2 or 4 in creature_template for the creature that you're targeting) (Run in World Database)
SET
@health = 100000, -- Choose how much hp you want here
@mana = 100000, -- Choose how much mana you want here
@armor = 100000, -- Choose how much armor you want here
@damage = 100000, -- Choose how much damage you want here
@entry = 28948; -- Set Creature Entry ID here

UPDATE creature_template ct
INNER JOIN creature_classlevelstats cc
ON ct.unit_class = cc.class
SET ct.HealthModifier = @health / CASE
WHEN ct.exp = 0 THEN cc.basehp0
WHEN ct.exp = 1 THEN cc.basehp1
WHEN ct.exp = 2 THEN cc.basehp2
END,
ct.ManaModifier = @mana / cc.basemana,
ct.ArmorModifier = @armor / cc.basearmor,
ct.DamageModifier = @damage / CASE
WHEN ct.exp = 0 THEN cc.damage_base
WHEN ct.exp = 1 THEN cc.damage_exp1
WHEN ct.exp = 2 THEN cc.damage_exp2
END
WHERE ct.entry = @entry AND cc.level = ROUND((ct.minlevel + ct.maxlevel)/2, 1);
 

Jenniy

I collect profile flairs.
Silver Supporter
Senior User
351
2020
70
Pretty cool set of queries you've got here! The character-inventory one can be especially useful when trying to deal with buggy items.
 
Top