diff options
| author | Kitzunu <24550914+Kitzunu@users.noreply.github.com> | 2021-07-25 03:34:08 +0200 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2021-07-25 03:34:08 +0200 |
| commit | 27916a62629a376b4600cc0514609f67772d6376 (patch) | |
| tree | 10d0ae071c5476eed40f0855d7178a586cfb8565 /docs | |
| parent | 460c138036738ef5bb7821013f52a4f9a47cc0e1 (diff) | |
| download | wiki-27916a62629a376b4600cc0514609f67772d6376.tar.gz wiki-27916a62629a376b4600cc0514609f67772d6376.tar.bz2 wiki-27916a62629a376b4600cc0514609f67772d6376.zip | |
chore: update useful-sql.md
Diffstat (limited to 'docs')
| -rw-r--r-- | docs/useful-sql.md | 26 |
1 files changed, 13 insertions, 13 deletions
diff --git a/docs/useful-sql.md b/docs/useful-sql.md index 78730a5..7b7f660 100644 --- a/docs/useful-sql.md +++ b/docs/useful-sql.md @@ -2,14 +2,14 @@ tableofcontents: 1 --- -# Useful SQL snippets: +# Useful SQL snippets ## Introduction These SQL queries are intended to be fast and easy-to-use tools that can help identify and troubleshoot problems in the AzerothCore database. They're here to be a quick reference to help when looking at DB-related problems. If anyone else has any similar useful queries, let me know and I'll add them. Similarly, if you have a common problem for which you'd like a query written, feel free to get in touch. -### Loot and Reference Loot Issues +## Loot and Reference Loot Issues -#### Find what creatures drop an item directly +### Find what creatures drop an item directly Note that this doesn't count items dropped from RLTs. ```sql SELECT ct.name, clt.chance, ct.maxlevel, it.ItemLevel @@ -19,7 +19,7 @@ JOIN `item_template` it ON clt.item = it.entry WHERE it.entry = XXXXX; ``` -#### Find which RLTs an item is in +### Find which RLTs an item is in ```sql SELECT rlt.entry, it.entry, it.name FROM `reference_loot_template` rlt @@ -27,7 +27,7 @@ JOIN `item_template` it ON rlt.Item = it.entry WHERE it.entry = XXXXX; ``` -#### Find which creatures share a RLT +### Find which creatures share a RLT Non-recursive. ```sql SELECT distinct ct.entry, ct.name @@ -36,7 +36,7 @@ JOIN `creature_loot_template` clt ON ct.lootid = clt.entry WHERE clt.Reference = XXXXX; ``` -#### Find number of items and level statistics for items in a given RLT +### Find number of items and level statistics for items in a given RLT This is useful when determining which creatures should have a particular RLT in their drop tables. ```sql SELECT COUNT(rlt.Item), MIN(it.ItemLevel), MAX(it.ItemLevel), AVG(it.ItemLevel) @@ -45,7 +45,7 @@ JOIN `reference_loot_template` rlt ON it.entry = rlt.item WHERE rlt.entry = XXXXX; ``` -#### Recursively find an item by name +### Recursively find an item by name Note this is complex enough that Keira won't run it properly - you will need to run it from a MySQL command prompt. Thanks to @anguaive for this one. ```sql SET @ITEM_NAME := 'insert name of item here'; @@ -75,7 +75,7 @@ JOIN `creature_template` ct ON ct.lootid = q.entry ORDER BY ct.name; ``` -### Miscellaneous Issues +## Miscellaneous Issues #### Find creature information from a spawn GUID Bug reports sometimes just reference a GUID with no other information about an NPC. This will find the creature a GUID belongs to. ```sql @@ -85,7 +85,7 @@ JOIN `creature` c ON ct.entry = c.id WHERE c.guid = XXXXX; ``` -#### Find all static creatures given a name +### Find all static creatures given a name You can just use part of the NPC's name (as here, 'Gordunni') and it will find all NPCs with that string in their name. ```sql SELECT c.guid, ct.name @@ -94,7 +94,7 @@ JOIN `creature_template` ct ON ct.entry = c.id WHERE c.movementtype = 0 AND ct.name LIKE '%gordunni%'; ``` -#### Find creatures that use a certain spell +### Find creatures that use a certain spell Note this is a bit rough and ready, and only works if the spell is in their first action slot. In this case, XXXX is the spell ID. ```sql SELECT ct.entry, ct.name, ct.maxlevel, ss.action_param1 @@ -103,7 +103,7 @@ JOIN `smart_scripts` ss ON ct.entry = ss.entryorguid WHERE ss.action_param1 = XXXXX; ``` -#### Find average wander distance for a creature +### Find average wander distance for a creature Handy for fixing static creatures. ```sql SELECT c.id, AVG(c.wander_distance) @@ -111,14 +111,14 @@ FROM `creature` c WHERE c.id = XXXX; ``` -#### Find other members of a node pool +### Find other members of a node pool Given a node GUID, find if it belongs to a node pool and list the other members. ```sql SELECT * FROM `pool_gameobject` WHERE `pool_entry` IN ( SELECT `pool_entry` from `pool_gameobject` WHERE guid = XXXX); ``` -#### Find respawn timers for a class of object +### Find respawn timers for a class of object Given a object's name, find the respawn timers for all spawns that match that name string. ```sql SELECT got.name, go.guid, go.spawntimesecs |
