[Release][Query] List All _Items and who they belong too

[Timlock]

Ok... this query is fun... its not really useful in any systems because its not made for performance... but it will give you a list of every _Item in your database.... and it will tell you who it belongs too...

this query works on char invo, avatar invo, pet invo, chest storage and guild storage....

I dont think there is anywhere else to store items,... except for open market... but we wont go there...

the query...

 Spoiler

USE [SRO_VT_SHARD1]

SELECT it.[ID64]
      ,oc.[ID]
      ,oc.[CodeName128]
      ,it.[OptLevel]
      ,it.[Variance]
      ,it.[Data]
	  ,iv_ch.[CharName16] InvoCharName
	  ,iv.[Slot] InvoSlot
	  ,av_ch.[CharName16] AvaCharName
	  ,av.[Slot] AvaSlot
	  ,pet_ch.[CharName16] + ' - ' + pet.[CharName] COLLATE Latin1_General_CI_AS PetCharName
	  ,iv_pt.[Slot] PetSlot
	  ,ac.[AccountID] AccountName
	  ,chest.[Slot] ChestSlot
	  ,iv_guild.[Name] GuildName
	  ,guild.[Slot] GuildSlot
  FROM [dbo].[_Items] AS it
  LEFT JOIN [dbo].[_RefObjCommon] oc ON oc.ID = it.RefItemID
  LEFT JOIN [dbo].[_Inventory] iv ON iv.[ItemID] = it.[ID64]
  LEFT JOIN [dbo].[_Char] iv_ch on iv_ch.[CharID] = iv.[CharID]
  LEFT JOIN [dbo].[_InventoryForAvatar] av ON av.[ItemID] = it.[ID64]
  LEFT JOIN [dbo].[_Char] av_ch on av_ch.[CharID] = av.[CharID]
  LEFT JOIN [dbo].[_InvCOS] iv_pt ON iv_pt.[ItemID] = it.[ID64]
  LEFT JOIN [dbo].[_CharCOS] AS pet ON pet.[ID] = iv_pt.[COSID]
  LEFT JOIN [dbo].[_Items] AS char_it ON char_it.[Data] = pet.[ID] AND char_it.RefItemID IN (SELECT [ID] FROM [SRO_VT_SHARD1].[dbo].[_RefObjCommon] WHERE [Service] = 1 AND [TypeID1] = 3 AND [TypeID2] = 2 AND [TypeID3] = 1 AND [TypeID4] = 2)
  LEFT JOIN [dbo].[_Inventory] AS char_iv ON char_iv.[ItemID] = char_it.[ID64]
  LEFT JOIN [dbo].[_Char] AS pet_ch on pet_ch.[CharID] = char_iv.[CharID]
  LEFT JOIN [dbo].[_Chest] chest ON chest.[ItemID] = it.[ID64]
  LEFT JOIN [dbo].[_AccountJID] ac on ac.[JID] = chest.[UserJID]
  LEFT JOIN [dbo].[_GuildChest] guild ON guild.[ItemID] = it.[ID64]
  LEFT JOIN [dbo].[_Guild] iv_guild ON iv_guild.[ID] = guild.[GuildID]
  WHERE it.[ID64] > 0
  ORDER BY it.[ID64] ASC

There are 5 name columns, depending where the item is... one of the columns will show the name, the other 4 will be null... if the item is on the ground then all 5 columns will be null.

results will look like this....

you can see simulation has some scrolls, and i have a sun glavie..

[*Silverlight*]

And the optimized version:

 Spoiler

USE [SRO_VT_SHARD]

SELECT TOP 1000 it.[ID64]
,oc.[ID]
,oc.[CodeName128]
,it.[OptLevel]
,it.[Variance]
,it.[Data]
,iv_ch.[CharName16] InvoCharName
,iv.[Slot] InvoSlot
,av_ch.[CharName16] AvaCharName
,av.[Slot] AvaSlot
,pet_ch.[CharName16] + ' - ' + pet.[CharName] COLLATE Latin1_General_CI_AS PetCharName
,iv_pt.[Slot] PetSlot
,ac.[AccountID] AccountName
,chest.[Slot] ChestSlot
,iv_guild.[Name] GuildName
,guild.[Slot] GuildSlot
FROM [dbo].[_Items] it WITH (NOLOCK)
LEFT JOIN [dbo].[_RefObjCommon] oc WITH (NOLOCK) ON oc.ID = it.RefItemID
LEFT JOIN [dbo].[_Inventory] iv WITH (NOLOCK) ON iv.[ItemID] = it.[ID64]
LEFT JOIN [dbo].[_Char] iv_ch WITH (NOLOCK) on iv_ch.[CharID] = iv.[CharID]
LEFT JOIN [dbo].[_InventoryForAvatar] av WITH (NOLOCK) ON av.[ItemID] = it.[ID64]
LEFT JOIN [dbo].[_Char] av_ch WITH (NOLOCK) on av_ch.[CharID] = av.[CharID]
LEFT JOIN [dbo].[_InvCOS] iv_pt WITH (NOLOCK) ON iv_pt.[ItemID] = it.[ID64]
LEFT JOIN [dbo].[_CharCOS] AS pet WITH (NOLOCK) ON pet.[ID] = iv_pt.[COSID]
LEFT JOIN [dbo].[_Items] AS char_it WITH (NOLOCK) ON char_it.[Data] = pet.[ID] AND char_it.RefItemID IN (SELECT [ID] FROM [SRO_VT_SHARD].[dbo].[_RefObjCommon] WITH (NOLOCK) WHERE [Service] = 1 AND [TypeID1] = 3 AND [TypeID2] = 2 AND [TypeID3] = 1 AND [TypeID4] = 2)
LEFT JOIN [dbo].[_Inventory] AS char_iv WITH (NOLOCK) ON char_iv.[ItemID] = char_it.[ID64]
LEFT JOIN [dbo].[_Char] AS pet_ch WITH (NOLOCK) on pet_ch.[CharID] = char_iv.[CharID]
LEFT JOIN [dbo].[_Chest] chest WITH (NOLOCK) ON chest.[ItemID] = it.[ID64]
LEFT JOIN [dbo].[_AccountJID] ac WITH (NOLOCK) on ac.[JID] = chest.[UserJID]
LEFT JOIN [dbo].[_GuildChest] guild WITH (NOLOCK) ON guild.[ItemID] = it.[ID64]
LEFT JOIN [dbo].[_Guild] iv_guild WITH (NOLOCK) ON iv_guild.[ID] = guild.[GuildID]
WHERE it.[ID64] > 0
ORDER BY it.[ID64] ASC

Thx for the shre