Quote Originally Posted by Tok124
I wrote a query a few days ago to inspect a characters inventory and i figured out that maybe someone else will need a query that can do this so i decided to share it !
SQL Query
Code:
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;
The query will return Item Entry, Item Name, Character Name, Amount and Stack Amount.
The reason why there is amount and stack amount is because lets say you have Rabbit's Foot x10 in a stack it will only show as 1 in amount and 10 in stackAmount but if you separate them it will show as 10 in Amount and (null) in stackAmount

Run in characters database and do not forget to edit the name in green text, And do NOT remove quotes !

Also, remember that this is a SELECT query so it wont change anything in your database therefore there is no need to create a backup. If anything goes wrong all that will happen is that it wont show the result. But the query has been tested !


Credits Tok124.





› See More: Inventory Inspector SQL Query