[SHARE] Usefull SQL scripts V2

[Sido]

Where is ur credits?

 

Thx to badriniki

 

here

[SuFFLeR]

here

Ok

[EdenEternal]

Updated 11/07/2010

 

Full Database Delete

DROP DATABASE `Database_name`;

 

Delete from droplist all items except 57 and 4037(or that items you want)

DELETE FROM droplist WHERE itemId NOT IN (57,4037);

[Sido]

Updated 11/07/2010

 

Full Database Delete

DROP DATABASE `Database_name`;

 

Delete from droplist all items except 57 and 4037(or that items you want)

DELETE FROM droplist WHERE itemId NOT IN (57,4037);

 

good , keep it up.

if somebody want, give this guy +1 karma instead of me (ofc in near future, if he still update this thread).

[EdenEternal]

Updated 11/07/2010

 

Script of Server Cleaning

UPDATE characters SET online=0;
DELETE FROM characters WHERE lastAccess < 1191211200;
DELETE FROM accounts WHERE lastactive < 1191211200;
DELETE FROM characters WHERE level < 75;
DELETE FROM characters WHERE account_name NOT IN (SELECT login FROM accounts);
DELETE FROM accounts WHERE login NOT IN (SELECT account_name FROM characters);
DELETE FROM clan_data WHERE leader_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM clan_privs WHERE clan_id NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM clan_subpledges WHERE clan_id NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM clan_wars WHERE clan1 NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM clan_wars WHERE clan2 NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM auction_bid WHERE bidderId NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM clanhall_functions WHERE hall_id NOT IN (SELECT ID FROM clanhall WHERE ownerId <> '0');
UPDATE clanhall SET paidUntil='0' WHERE ownerId NOT IN (SELECT clan_id FROM clan_data);
UPDATE clanhall SET ownerId='0' WHERE ownerId NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM account_data WHERE account_name NOT IN (SELECT login FROM accounts);
DELETE FROM account_data WHERE account_name NOT IN (SELECT account_name FROM characters);
DELETE FROM account_data WHERE value NOT IN (SELECT obj_Id FROM characters);
DELETE FROM items WHERE loc <> 'clanwh' and owner_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM items WHERE loc = 'clanwh' and owner_id NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM character_skills WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_skills_save WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_shortcuts WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_recipebook WHERE char_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_quests WHERE char_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_macroses WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_hennas WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_friends WHERE char_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_subclasses WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM couples WHERE player1Id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM couples WHERE player2Id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM pets where item_obj_id not in (SELECT object_id FROM items);
DELETE FROM seven_signs WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM forums WHERE forum_owner_id <> '0' AND forum_owner_id NOT IN (SELECT clan_id FROM clan_data);

 

Adena Drop Chance 100%(Default 70%)

UPDATE `droplist` SET `droplist`.`chance`=1000000 WHERE `droplist`.`itemId`=57;

 

-------------------------------------------------------------------------------

 

Change all server npc Tittle's

update npc SET title = 'www.MaxCheaters.Com' where id between 1 and 99999;
update npc SET serverSideTitle = '1' where id between 1 and 99999;
update npc SET serverSideName = '1' where id between 1 and 99999;

 

Credits to Drasius

 

good , keep it up.

if somebody want, give this guy +1 karma instead of me (ofc in near future, if he still update this thread).

 

nice big thanks for appraise :)

[bubulina]

good , keep it up.

if somebody want, give this guy +1 karma instead of me (ofc in near future, if he still update this thread).

for some  c/p ?? i got ban for it... =))..

 

on@ keep it up!

[Sido]

for some  c/p ?? i got ban for it... =))..

i did not banned u :P

i find his shares useful , even if they are not made by him, i dont lose time to search them cuz now i can find all of them in one topic :P so yes i would give my karma if i would be able.

[Magaiveris]

Your shares is awesome dude ! Thanks for all SQL scripts really ! Now, using the most

[kostantinosftw]

VEry Nc Post ! thank you ! =]

[disorder25]

DELETE FROM characters WHERE lastAccess < 466560000;

DELETE FROM accounts WHERE lastactive < 466560000;

 

Are you sure this is for 3 months?

If I want like 6 months what would that be?

Thank you.

[+cs.tribal]

awesome and really usefull scripts mate!

some of them will help you balance your server and some will help modify some things without java codes!

 

 

 

 

 

 

[sladurabg]

awesome share keep it up dude!  8)