1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
|
---
tableofcontents: 1
---
# 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
### 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
FROM `creature_template` ct
JOIN `creature_loot_template` clt ON ct.lootid = clt.entry
JOIN `item_template` it ON clt.item = it.entry
WHERE it.entry = XXXXX;
```
### Find which RLTs an item is in
```sql
SELECT rlt.entry, it.entry, it.name
FROM `reference_loot_template` rlt
JOIN `item_template` it ON rlt.Item = it.entry
WHERE it.entry = XXXXX;
```
### Find which creatures share a RLT
Non-recursive.
```sql
SELECT distinct ct.entry, ct.name
FROM `creature_template` ct
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
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)
FROM `item_template` it
JOIN `reference_loot_template` rlt ON it.entry = rlt.item
WHERE rlt.entry = XXXXX;
```
### 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';
SET @ITEM_ID := (SELECT `entry` FROM `item_template` WHERE `name` = @ITEM_NAME);
SELECT DISTINCT ct.name AS `creature`, @ITEM_NAME as `item` FROM (
WITH RECURSIVE cte (`entry`, `item`, `reference`) AS (
SELECT `entry`, `item`, `reference`
FROM `reference_loot_template`
WHERE `item` = @ITEM_ID
UNION ALL
SELECT r.entry, r.item, r.reference
FROM `reference_loot_template` r
INNER JOIN cte
ON r.reference = cte.entry
)
SELECT clt.entry
FROM cte
JOIN `creature_loot_template` clt ON clt.reference = cte.entry
UNION ALL
SELECT entry
FROM
`creature_loot_template`
WHERE item = @ITEM_ID
) AS q
JOIN `creature_template` ct ON ct.lootid = q.entry
ORDER BY ct.name;
```
## 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
SELECT ct.entry, ct.name, ct.minlevel, ct.maxlevel
FROM `creature_template` ct
JOIN `creature` c ON ct.entry = c.id
WHERE c.guid = XXXXX;
```
### 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
FROM `creature` c
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
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
FROM `creature_template` ct
JOIN `smart_scripts` ss ON ct.entry = ss.entryorguid
WHERE ss.action_param1 = XXXXX;
```
### Find average wander distance for a creature
Handy for fixing static creatures.
```sql
SELECT c.id, AVG(c.wander_distance)
FROM `creature` c
WHERE c.id = XXXX;
```
### 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
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
FROM `gameobject_template` got
JOIN `gameobject` go ON got.entry = go.id
WHERE name LIKE '%name-of-object%'
ORDER BY go.spawntimesecs
```
|
