[Release] inactive characters remover procedure

[KingDollar]

well after i saw some people request this function i decided to help
as topic says this procedure to remove inactive characters with your specified days

proc

 Spoiler

CREATE PROC _InActive_Character_Remover
@ INT,
@Level tinyint,
@ tinyint
AS
SET XACT_ABORT ON
SET NOCOUNT ON
/*
simple inactive character remove
version 1.1
all credit goes to king dollar
*/

--DECLARING SHITS
DECLARE @D INT
DECLARE @ varchar(64)




BEGIN TRY
BEGIN TRANSACTION
DECLARE CharShit CURSOR LOCAL FOR SELECT CharID,CharName16 FROM SRO_VT_SHARD.._Char WHERE CONVERT(VARCHAR(30),DATEDIFF(DAY,GETDATE(),LastLog out)) >= '-'+CONVERT(VARCHAR(30) @) AND CharName16 != 'd' and CurLevel between @Level and @
OPEN CharShit;
FETCH NEXT FROM CharShit INTO @D @;
WHILE @@FETCH_STATUS = 0
BEGIN

print 'Deleted ' + @ + ' Reason : because it wasn''t active for more than '+CONVERT(VARCHAR(30) @) +' days and level between '+CONVERT(VARCHAR(30) @Level) +' and '+CONVERT(VARCHAR(30) @)
DELETE FROM SRO_VT_SHARD.._User WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._GuildMember WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._GPHistory WHERE CharName = @
DELETE FROM SRO_VT_SHARD.._TrainingCampMember WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._TrainingCamp WHERE ID IN(SELECT CampID FROM _TrainingCampMember WHERE CharID = @D)
DELETE FROM SRO_VT_SHARD.._TrainingCampBuffStatus WHERE CampID IN(SELECT CampID FROM _TrainingCampMember WHERE CharID = @D)
DELETE FROM SRO_VT_SHARD.._TrainingCampHonorRank WHERE CampID IN(SELECT CampID FROM _TrainingCampMember WHERE CharID = @D)
DELETE FROM SRO_VT_SHARD.._TrainingCampSubMentorHonorPoint WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._CharNameList WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._CharNickNameList WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._BlackNameList WHERE BlacklistName = @
DELETE FROM SRO_VT_SHARD.._BlockedWhisperers WHERE OwnerID = @D OR TargetName = @
DELETE FROM SRO_VT_SHARD.._CharCollectionBook WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._CharInstanceWorldData WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._CharQuest WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._CharSkill WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._CharSkillMastery WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._CharTrijobSafeTrade WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._CharTrijob WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._TimedJob WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._DeletedChar WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._ClientConfig WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._TimedJobForPet WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._TimedJob WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._FleaMarketNetwork WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._Friend WHERE CharID = @D OR FriendCharName = @
DELETE FROM SRO_VT_SHARD.._BindingOptionWithItem WHERE nItemDBID IN(SELECT ItemID FROM SRO_VT_SHARD.._InventoryForAvatar WHERE CharID = @D) OR nItemDBID IN(SELECT ItemID FROM SRO_VT_SHARD.._Inventory WHERE CharID = @D) OR nItemDBID IN(SELECT ItemID FROM SRO_VT_SHARD.._InvCOS WHERE COSID IN (SELECT ID FROM SRO_VT_SHARD.._CharCOS WHERE OwnerCharID = @D OR OwnerCharID = 0))
DELETE FROM SRO_VT_SHARD.._InvCOS WHERE COSID IN (SELECT ID FROM _CharCOS WHERE OwnerCharID = @D OR OwnerCharID = 0)
DELETE FROM SRO_VT_SHARD.._Inventory WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._InventoryForAvatar WHERE CharID = @D
UPDATE SRO_VT_SHARD.._Chest SET ItemID = 0 WHERE ITEMID IN(SELECT ID64 FROM SRO_VT_SHARD.._Items WHERE ID64 NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._InventoryForAvatar) AND ID64 NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._Inventory) AND ID64 NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._InvCOS) AND ID64 > 0)
UPDATE SRO_VT_SHARD.._OpenMarket SET ItemID = 0 WHERE ITEMID NOT IN(SELECT ID64 FROM SRO_VT_SHARD.._Items WHERE ID64 NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._InventoryForAvatar) AND ID64 NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._Inventory) AND ID64 NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._InvCOS) AND ID64 > 0)
DELETE FROM SRO_VT_SHARD.._ItemPool WHERE ItemID NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._InventoryForAvatar) AND ItemID NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._Inventory) AND ItemID NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._InvCOS) AND ItemID > 0
DELETE FROM SRO_VT_SHARD.._Items WHERE ID64 NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._InventoryForAvatar) AND ID64 NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._Inventory) AND ID64 NOT IN(SELECT ItemID FROM SRO_VT_SHARD.._InvCOS) AND ID64 > 0
DELETE FROM SRO_VT_SHARD.._OpenMarket WHERE CharName16 = @ and ITEMID = 0
DELETE FROM SRO_VT_SHARD.._Memo WHERE CharID = @D OR FromCharName = @
DELETE FROM SRO_VT_SHARD.._CharCOS WHERE OwnerCharID = @D
DELETE FROM SRO_VT_SHARD.._StaticAvatar WHERE CharID = @D
DELETE FROM SRO_VT_SHARD.._Char WHERE CharID = @D
FETCH NEXT FROM CharShit INTO @D @;
END;
CLOSE CharShit;
DEALLOCATE CharShit;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState,ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage
ROLLBACK TRANSACTION
END
END CATCH
GO

usage

 Spoiler

exec _InActive_Character_Remover '30' /*days that you want at least be characters inactive time*/,'1' /*min level that you want at least be characters inactive level */ ,'50' /*max level that you want at least be characters inactive level*/

greeting

[Hypnos¥]

This will help servers with 20k+ characters which take long time to backup their Database , helpful thanks

[KingDollar]

Originally Posted by Aouli

This will help servers with 20k+ characters which take long time to backup their Database , helpful thanks

you are welcome
and ye i hope it help server owners

[WickedNite.]

It'd be better if characters are deleted by level, not by offline time.

[KingDollar]

Originally Posted by hazemooking

if someone is away from the game for 1 month and decided to come back he find his character removed Good Job Alex****

well it's not forcing you can decide the days as you need

Originally Posted by TheWickedNite

It'd be better if characters are deleted by level, not by offline time.

umm well would be more better if with level and offline time

[WickedNite.]

I'm not so sure about offline time, if players want to come back they lost everything.

[KingDollar]

Originally Posted by TheWickedNite

I'm not so sure about offline time, if players want to come back they lost everything.

well bro there is servers are online since 2011 so they have to clean their dbs
if character does not open his char for like 365 days which means for 1 year
so i will delete it you know sure he leave the game

if i do it with level only so there is low level in srv which are leveling up in days so it will delete you know

[D.Sro]

tested and unworking

[KingDollar]

Originally Posted by D.Sro

tested and unworking

what is your error then

[xxnukertube]

awesome King$
Really thank you

[KingDollar]

--updated version 1.1 changelog
added min level that you want at least be characters inactive level
added maxlevel that you want at least be characters inactive level
fixed some errors on code

[xxnukertube]

Originally Posted by Alexiuns*

--updated version 1.1 changelog
added min level that you want at least be characters inactive level
added maxlevel that you want at least be characters inactive level
fixed some errors on code

Thanks

[KingDollar]

Originally Posted by xxnukertube

Thanks

you are welcome sir

[florian0]

Originally Posted by Alexiuns*

well it's not forcing you can decide the days as you need

And since most servers collect your email during registration, you may send a reminder before deleting the character entirely.
Bad luck for people who entered an invalid/trash mail address :P

[KingDollar]

Originally Posted by florian0

And since most servers collect your email during registration, you may send a reminder before deleting the character entirely.
Bad luck for people who entered an invalid/trash mail address :P

well ye i can do it but you have to setup your own smtp server