[Release]Bunch of Useful Queries for MsSql Database Return Results

whyt3boi · 02/14/2012, 08:37

- Finds Inventory Items Based on Char name and some additional info

Code:

Use SRO_VT_SHARD_INIT 
Select CH.CharName16, INV.CharID, INV.ItemID, IT.OptLevel, IT.RefItemID, INV.Slot, REF.ReqGender, REFC.ReqLevel1, REFC.CodeName128 From _Inventory As INV  
Right Join _Items As IT On INV.ItemID = IT.ID64
Right Join _RefObjItem As REF On IT.RefItemID = REF.ID
Right Join _RefObjCommon As REFC On REFC.ID = REF.ID
Right Join _Char As CH On CH.CharID = INV.CharID
Where CH.CharName16 = 'Pal2aDoX' And INV.Slot <= '12'
Order By Slot Asc

(If you understand the queries you can change _Inventory to _Chest, GuildChest, InvCOS to check other storage places for items. You can also remove the <='12' so it shows all items etc.)

- Finds Items Not Wanted or otherwise (Used for finding items to delete)

Code:

Use SRO_VT_SHARD_INIT
Select IT.RefItemID, REF.CodeName128, IT.ID64 From _RefObjCommon AS Ref
Right Join _Items As IT On IT.RefItemID = Ref.ID
Where Ref.CodeName128 Like 'ITEM_CH_%09%'
Or REF.CodeName128 Like 'ITEM_CH_%10%'
Or REF.CodeName128 Like 'ITEM_EU%' 
Order By CodeName128 Asc

- Finds items in inventory based on stated item

Code:

Use SRO_VT_SHARD_INIT
Select IT.ID64, IT.OptLevel As Plus, INV.CharID, CH.CharName16, REF.CodeName128 From _RefObjCommon As REF
Right Join _Items As IT On IT.RefItemID = REF.ID
Right Join _Inventory As INV On INV.ItemID = IT.ID64
Right Join _Char as CH On CH.CharID = INV.CharID
Where CodeName128 like '%SHIELD_08_C_RARE'

- Searches for mobs

Code:

Use SRO_VT_SHARD_INIT
Select REF.CodeName128, REF.ID, REF.Link, CH.Lvl From _RefObjCommon AS REF
Right Join _RefObjChar As CH On CH.ID = REF.Link
Where REF.CodeName128 like 'MOB_SD%' 
and CH.Lvl Between 105 and 109 
and REF.Rarity Not Between 3 and 9
Order By CH.Lvl Asc

(Ignores uniques and searches between mob levels 105-109. You can change these 2 to what ever you like)

- Finds registered guilds for fw, leader, fw name, etc

Code:

Use SRO_VT_SHARD_INIT
Select Distinct SG.GuildID, GM.CharName as GuildMaster, G.Name as GuildName, SG.FortressID, REFSG.CodeName128 As FortressName From _SiegeFortressRequest as SG
Right Join _RefSiegeFortress As REFSG On SG.FortressID = REFSG.FortressID
Right Join _Guild as G On G.ID = SG.GuildID
Right Join _RefSiegeFortress on REFSG.FortressID = SG.FortressID
Right Join _GuildMember as GM On GM.GuildID = SG.GuildID 
Where GM.MemberClass = 0 and SG.RequestType = 0
Order By REFSG.CodeName128 Asc

- Resets Guild penalty when leaving

Code:

Delete From _TimedJob Where ChardID = 'CharID' and JobID = '1'

- Finds Username, jid, charname based from charname

Code:

Select SRO_VT_SHARD_INIT.dbo._Char.CharName16 As CharName, U.StrUserID As UserName, UserJID As JID From SRO_VT_SHARD_INIT.dbo._User 
Right Join SRO_VT_SHARD_INIT.dbo._Char
On SRO_VT_SHARD_INIT.dbo._User.CharID = SRO_VT_SHARD_INIT.dbo._Char.CharID
Right Join SRO_VT_ACCOUNT.dbo.TB_User As U
On U.JID = SRO_VT_SHARD_INIT.dbo._User.UserJID
Where _Char.CharName16 = 'CharNameHere'

- Finds Item Names and Durability for Adding to RefScrap

Code:

Use SRO_VT_SHARD_INIT
Select REF.CodeName128, I.Dur_L From _RefObjCommon As REF
Right Join _RefObjItem As I On I.ID = REF.Link
Where REF.CodeName128 like 'ITEM_CH_W_%_03_%_C_RARE'
and REF.CodeName128 Not Like '%_HA_%'
Order By REF.CodeName128 Asc

maroelking · 05/21/2013, 22:50

NICE Thanks

~~Alexiuns~~ · 11/10/2013, 22:36

nice but query of - Finds Inventory Items Based on Char name and some additional info
let me fix it to you ^_^

Spoiler

Dev Power · 11/11/2013, 10:18

NICE Thanks

wilber1414 · 12/07/2015, 07:30

anyone know the Query to place again the Job/Guild Penalty?