[RELEASE] Item Removal From Multiple Tables

[BodySalvage]

I have been asked several times in the past for a script like this and figured I would release it for the community. Its a really easy way to resolve a bad patch situation or various other possible situations where items appear in player possession that should not be in the game based on your desired setup. This will quickly remove an item from Guild WH, Player WH, AH, and character inventory.

Remove By ItemID:

 Spoiler

SET NOCOUNT ON;
DECLARE
@ItemID int,
@CharItems int,
@PlayerWH int,
@GuildWH int,
@Auction int

SET @ItemID = '';/* Edit this to contain the ItemID of the Item you wish to remove */
SET @CharItems=0;
SET @PlayerWH=0;
SET @GuildWH=0;
SET @Auction=0;

IF (@ItemID='')
BEGIN
PRINT 'Please provide an ItemID';
END
ELSE IF (@ItemID != '')
BEGIN
DELETE FROM PS_GameData.dbo.CharItems WHERE ItemID = @ItemID;
SELECT @CharItems = @@ROWCOUNT
DELETE FROM PS_GameData.dbo.UserStoredItems WHERE ItemID = @ItemID;
SELECT @PlayerWH = @@ROWCOUNT
DELETE FROM PS_GameData.dbo.GuildStoredItems WHERE ItemID = @ItemID;
SELECT @GuildWH = @@ROWCOUNT
DELETE FROM PS_GameData.dbo.MarketItems WHERE ItemID = @ItemID;
SELECT @Auction = @@ROWCOUNT

/* Delete checks */
IF (@CharItems>0)
BEGIN
PRINT 'Successfully removed '+CONVERT(varchar,@CharItems)+' Item(s) from Character Inventories'
END
ELSE
BEGIN
PRINT 'No Item(s) detected in Character Inventories'
END
IF (@PlayerWH>0)
BEGIN
PRINT 'Successfully removed '+CONVERT(varchar,@PlayerWH)+' Item(s) from Warehouses'
END
ELSE
BEGIN
PRINT 'No Item(s) detected in Player Warehouses'
END
IF (@GuildWH>0)
BEGIN
PRINT 'Successfully removed '+CONVERT(varchar,@GuildWH)+' Item(s) from Guild Warehouses'
END
ELSE
BEGIN
PRINT 'No Item(s) detected in Guild Warehouses'
END
IF (@Auction>0)
BEGIN
PRINT 'Successfully removed '+CONVERT(varchar,@Auction)+' Item(s) from Auction Boards'
END
ELSE
BEGIN
PRINT 'No Item(s) detected in Auction Boards'
END
END

Remove By ItemUID:

 Spoiler

SET NOCOUNT ON;
DECLARE
@ItemUID bigint,
@CharItems int,
@PlayerWH int,
@GuildWH int,
@Auction int

SET @ItemUID = '';/* Edit this to contain the ItemUID of the Item you wish to remove */
SET @CharItems=0;
SET @PlayerWH=0;
SET @GuildWH=0;
SET @Auction=0;

IF (@ItemUID='')
BEGIN
PRINT 'Please provide an ItemUID';
END
ELSE IF (@ItemUID != '')
BEGIN
DELETE FROM PS_GameData.dbo.CharItems WHERE ItemUID = @ItemUID;
SELECT @CharItems = @@ROWCOUNT
DELETE FROM PS_GameData.dbo.UserStoredItems WHERE ItemUID = @ItemUID;
SELECT @PlayerWH = @@ROWCOUNT
DELETE FROM PS_GameData.dbo.GuildStoredItems WHERE ItemUID = @ItemUID;
SELECT @GuildWH = @@ROWCOUNT
DELETE FROM PS_GameData.dbo.MarketItems WHERE ItemUID = @ItemUID;
SELECT @Auction = @@ROWCOUNT

/* Delete checks */
IF (@CharItems>0)
BEGIN
PRINT 'Successfully removed '+CONVERT(varchar,@CharItems)+' Item(s) from Character Inventories'
END
ELSE
BEGIN
PRINT 'No Item(s) detected in Character Inventories'
END
IF (@PlayerWH>0)
BEGIN
PRINT 'Successfully removed '+CONVERT(varchar,@PlayerWH)+' Item(s) from Warehouses'
END
ELSE
BEGIN
PRINT 'No Item(s) detected in Player Warehouses'
END
IF (@GuildWH>0)
BEGIN
PRINT 'Successfully removed '+CONVERT(varchar,@GuildWH)+' Item(s) from Guild Warehouses'
END
ELSE
BEGIN
PRINT 'No Item(s) detected in Guild Warehouses'
END
IF (@Auction>0)
BEGIN
PRINT 'Successfully removed '+CONVERT(varchar,@Auction)+' Item(s) from Auction Boards'
END
ELSE
BEGIN
PRINT 'No Item(s) detected in Auction Boards'
END
END

*UPDATE - Added basic checks to display messages according to the effected rows.

*NOTE I looked for a similar script release and found none, should this have been released in a different form then please close this thread.*

[Psycnosis]

good script but you should also remove from auction board its a common place to forget

[GMCronus]

i have to be stupid. but i place it here anyway..

i run this and it comes up with Please provide an ItemID

or that itemuid if i placve that there.. i place the itemuid in xxxxxx

SET @ItemID = 'xxxxxx';/* Edit this to contain the ItemID of the Item you wish to remove */

still i have the item in the database charitems and so on. what do i realy do wrong?

[danilokdn]

restart the server?

[GMCronus]

yes but should still not be in the db when you look for it? in the game yes but not in the db?

[BodySalvage]

Originally Posted by GMCronus

i have to be stupid. but i place it here anyway..

i run this and it comes up with Please provide an ItemID

or that itemuid if i placve that there.. i place the itemuid in xxxxxx

SET @ItemID = 'xxxxxx';/* Edit this to contain the ItemID of the Item you wish to remove */

still i have the item in the database charitems and so on. what do i realy do wrong?

When running this, use ItemID and not the ItemUID. If you want to remove a specific ItemUID then this is not the script you want. This removes a specific ItemID(like Etains "100001") from everyone. To make this work for UID's just add a "U" to all ItemID and change the int to bigint like below.

 Spoiler

DECLARE @ItemUID bigint

SET @ItemUID = '';/* Edit this to contain the ItemUID of the Item you wish to remove */

IF(@ItemUID='')
BEGIN
PRINT 'Please provide an ItemUID';
END
ELSE IF (@ItemUID != '')
BEGIN
DELETE FROM PS_GameData.dbo.CharItems WHERE ItemUID = @ItemUID;
DELETE FROM PS_GameData.dbo.UserStoredItems WHERE ItemUID = @ItemUID;
DELETE FROM PS_GameData.dbo.GuildStoredItems WHERE ItemUID = @ItemUID;
DELETE FROM PS_GameData.dbo.MarketItems WHERE ItemUID = @ItemUID;
END

** Added to initial release post as well.

[GMCronus]

ok let me try and explain this:

DECLARE @ItemID int

SET @ItemID = '100200';/* Edit this to contain the ItemID of the Item you wish to remove */

IF (@ItemID != NULL)
BEGIN
DELETE FROM PS_GameData.dbo.CharItems WHERE ItemID = @ItemID;
DELETE FROM PS_GameData.dbo.UserStoredItems WHERE ItemID = @ItemID;
DELETE FROM PS_GameData.dbo.GuildStoredItems WHERE ItemID = @ItemID;
DELETE FROM PS_GameData.dbo.MarketItems WHERE ItemID = @ItemID;
END
ELSE
BEGIN
PRINT 'Please provide an ItemID';
END

becouse i want to remove what i call DP Crystal from the game (just a test:-)

this gives me:

Please provide an ItemID

as a message when i run it..

then i run

SELECT
UserUID
,UserID
,c.CharID
,c.CharName
,i.ItemID
,i.ItemName
,SUM(ci.Count) as 'TotalCountOfItem'
FROM [PS_GameData].[dbo].[CharItems] ci with (nolock)
inner join [PS_GameDefs].[dbo].[Items] i with (nolock)
on i.ItemID = ci.ItemID
inner join [PS_GameData].[dbo].[Chars] c with (nolock)
on c.CharID = ci.CharID
where
i.ItemName like '%DP Crystal%' -- Insert part of item name here
group by
UserUID
,UserID
,c.CharID
,c.CharName
,i.ItemID
,i.ItemName
order by
i.ItemName asc, TotalCountOfItem desc, c.UserID asc, c.CharName asc

that still show the item ingame in many places (inventory of players)

so what did i do wrong?

should be removed here now right? from the db i mean or am i totaly off atm?