[SHARE] Usefull SQL scripts v3

[kasta]

keep sharing ^^

[Sokal]

Thanks Leki

[EdenEternal]

Updated 18/07/2010

 

Give All Clan Skills to Clan

INSERT INTO `clan_skills` VALUES
('id clana','370','3','Clan Vitality'),
('id clana','371','3','Clan Spirituality'),
('id clana','372','3','Clan Essence'),
('id clana','373','3','Clan Lifeblood'),
('id clana','374','3','Clan Morale'),
('id clana','375','3','Clan  Clarity'),
('id clana','376','3','Clan  Might'),
('id clana','377','3','Clan Aegis'),
('id clana','378','3','Clan Empowerment'),
('id clana','379','3','Clan Magic Protection'),
('id clana','380','3','Clan Guidance'),
('id clana','381','3','Clan Agility'),
('id clana','382','3','Clan Withstand-Attack'),
('id clana','383','3','Clan Shield Boost'),
('id clana','384','3','Clan Cyclonic Resistance'),
('id clana','385','3','Clan Magmatic Resistance'),
('id clana','386','3','Clan Fortitude'),
('id clana','387','3','Clan Freedom'),
('id clana','388','3','Clan Vigilance'),
('id clana','389','3','Clan March'),
('id clana','390','3','Clan Luck'),
('id clana','391','1','Clan Imperium');

[EdenEternal]

Updated 18/07/2010

 

Delete from Server All Data of Character that is not active more then 1 month.

DELETE FROM characters WHERE lastAccess < 155520000;
DELETE FROM character_quests WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM items WHERE owner_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_friends WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_shortcuts WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_hennas WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_macroses WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_recipebook WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_recommends WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_skills WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_skills_save WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_subclasses WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM heroes WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM olympiad_nobles WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM augmentations WHERE item_id=(SELECT item_id FROM items WHERE owner_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM auction_bid WHERE clan_name=(SELECT clan_name FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM auction_watch WHERE charObjId=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM clan_privs WHERE clan_id=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM clan_subpledges WHERE clan_id=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM clan_wars WHERE clan1=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM clan_wars WHERE clan2=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
UPDATE clanhall SET ownerId="0" WHERE ownerId=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM pets WHERE item_obj_id=(SELECT object_id FROM items WHERE owner_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM seven_signs WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM siege_clans WHERE clan_id=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));

Thx Realteк for idea

[assassinhachik]

Updated 18/07/2010

 

Delete from Server All Data of Character that is not active more then 1 month.

DELETE FROM characters WHERE lastAccess < 155520000;
DELETE FROM character_quests WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM items WHERE owner_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_shortcuts WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_skills WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_skills_save WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_subclasses WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);

Thx Realteк for idea

finally i found it thx you Leki!!

[EdenEternal]

finally i found it thx you Leki!!

No Problem, but my karma still is -3 :(

[EdenEternal]

Updated 21/07/2010

 

Delete all Retail Mobs [Thx For Idea HouLiGaN]

DELETE FROM spawnlist WHERE `npc_templateid` IN (SELECT id FROM npc WHERE type = 'L2Monster');

[Paran1x]

Thanks,very useful.

[AlexCardyz]

ty man :D

[Mart]

it would be better if u use some declare @ statements so people can edit easily

[v1hack]

Updated 18/07/2010

 

Delete from Server All Data of Character that is not active more then 1 month.

DELETE FROM characters WHERE lastAccess < 155520000;
DELETE FROM character_quests WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM items WHERE owner_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_friends WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_shortcuts WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_hennas WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_macroses WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_recipebook WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_recommends WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_skills WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_skills_save WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_subclasses WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM heroes WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM olympiad_nobles WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM augmentations WHERE item_id=(SELECT item_id FROM items WHERE owner_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM auction_bid WHERE clan_name=(SELECT clan_name FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM auction_watch WHERE charObjId=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM clan_privs WHERE clan_id=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM clan_subpledges WHERE clan_id=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM clan_wars WHERE clan1=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM clan_wars WHERE clan2=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
UPDATE clanhall SET ownerId="0" WHERE ownerId=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM pets WHERE item_obj_id=(SELECT object_id FROM items WHERE owner_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM seven_signs WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM siege_clans WHERE clan_id=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));

Thx Realteк for idea

 

don't understand something 155520000 is 180days in unix timestamp right? so how he get 1 month lol?