[Guide] How to clean spawnlist by level or type +SQL scripts explain.

[1tm]

Here is advanced guide, how to delete unwanted npc/mob from spawnlist. From here you will learn how to do: How to delete monster below/bigger x level from spawnlist.. How to delete all monster.. How to delete any type of npcs by type or level.. In seconds!

 

First of all we need do old npc.sql and spawnlist.sql backups. At navicat right click on npc.sql and then "Dump SQL file" then the same with spawnlist.sql:

 

 

[To insert backup: Right click at navicat, then "Execute SQL File", choose your backup and click start.]

 

Now we must decide what we want to delete from spawnlist.sql.

 

For example we want to delete all mobs from spawnlist.sql.

Then we need to know all mobs template ids. First of all we now need delete all non-monsters from npc.sql. Now we need delete all other npc except l2monster. We copy this text(there is no l2monster):

DELETE FROM npc WHERE type = 'L2Adventurer';
DELETE FROM npc WHERE type = 'L2AirShipController';
DELETE FROM npc WHERE type = 'L2Artefact';
DELETE FROM npc WHERE type = 'L2Auctioneer';
DELETE FROM npc WHERE type = 'L2BabyPet';
DELETE FROM npc WHERE type = 'L2CabaleBuffer';
DELETE FROM npc WHERE type = 'L2CastleBlacksmith';
DELETE FROM npc WHERE type = 'L2CastleChamberlain';
DELETE FROM npc WHERE type = 'L2CastleMagician';
DELETE FROM npc WHERE type = 'L2CastleTeleporter';
DELETE FROM npc WHERE type = 'L2CastleWarehouse';
DELETE FROM npc WHERE type = 'L2Chest';
DELETE FROM npc WHERE type = 'L2ClanHallManager';
DELETE FROM npc WHERE type = 'L2ClassMaster';
DELETE FROM npc WHERE type = 'L2ControlTower';
DELETE FROM npc WHERE type = 'L2Decoy';
DELETE FROM npc WHERE type = 'L2Doormen';
DELETE FROM npc WHERE type = 'L2FameManager';
DELETE FROM npc WHERE type = 'L2FeedableBeast';
DELETE FROM npc WHERE type = 'L2FestivalGuide';
DELETE FROM npc WHERE type = 'L2FestivalMonster';
DELETE FROM npc WHERE type = 'L2Fisherman';
DELETE FROM npc WHERE type = 'L2FlyMinion';
DELETE FROM npc WHERE type = 'L2FlyMonster';
DELETE FROM npc WHERE type = 'L2FlyNpc';
DELETE FROM npc WHERE type = 'L2FortBallista';
DELETE FROM npc WHERE type = 'L2FortCommander';
DELETE FROM npc WHERE type = 'L2FortEnvoy';
DELETE FROM npc WHERE type = 'L2FortLogistics';
DELETE FROM npc WHERE type = 'L2FortManager';
DELETE FROM npc WHERE type = 'L2FortSiegeGuard';
DELETE FROM npc WHERE type = 'L2FortSiegeNpc';
DELETE FROM npc WHERE type = 'L2FortSupportCaptain';
DELETE FROM npc WHERE type = 'L2FriendlyMob';
DELETE FROM npc WHERE type = 'L2GrandBoss';
DELETE FROM npc WHERE type = 'L2GroupLeader';
DELETE FROM npc WHERE type = 'L2Guard';
DELETE FROM npc WHERE type = 'L2ManorManager';
DELETE FROM npc WHERE type = 'L2MercenaryManager';
DELETE FROM npc WHERE type = 'L2Merchant';
DELETE FROM npc WHERE type = 'L2MerchantSummon';
DELETE FROM npc WHERE type = 'L2MercManager';
DELETE FROM npc WHERE type = 'L2Minion';

DELETE FROM npc WHERE type = 'L2Npc';
DELETE FROM npc WHERE type = 'L2NpcBuffer';
DELETE FROM npc WHERE type = 'L2NpcWalker';
DELETE FROM npc WHERE type = 'L2Observation';
DELETE FROM npc WHERE type = 'L2OlympiadManager';
DELETE FROM npc WHERE type = 'L2PenaltyMonster';
DELETE FROM npc WHERE type = 'L2Pet';
DELETE FROM npc WHERE type = 'L2PetManager';
DELETE FROM npc WHERE type = 'L2RaidBoss';
DELETE FROM npc WHERE type = 'L2RiftInvader';
DELETE FROM npc WHERE type = 'L2SepulcherMonster';
DELETE FROM npc WHERE type = 'L2SepulcherNpc';
DELETE FROM npc WHERE type = 'L2SiegeGuard';
DELETE FROM npc WHERE type = 'L2SiegeNpc';
DELETE FROM npc WHERE type = 'L2SiegeSummon';
DELETE FROM npc WHERE type = 'L2SignsPriest';
DELETE FROM npc WHERE type = 'L2SymbolMaker';
DELETE FROM npc WHERE type = 'L2TamedBeast';
DELETE FROM npc WHERE type = 'L2Teleporter';
DELETE FROM npc WHERE type = 'L2TownPet';
DELETE FROM npc WHERE type = 'L2Trainer';
DELETE FROM npc WHERE type = 'L2TransformManager';
DELETE FROM npc WHERE type = 'L2TvTEventNpc';
DELETE FROM npc WHERE type = 'L2VillageMaster';
DELETE FROM npc WHERE type = 'L2Warehouse';
DELETE FROM npc WHERE type = 'L2WeddingManager';
DELETE FROM npc WHERE type = 'L2WyvernManager';
DELETE FROM npc WHERE type = 'L2FortDoormen';
DELETE FROM npc WHERE type = 'L2FortWyvernManager';
DELETE FROM npc WHERE type = 'L2FameManager';
DELETE FROM npc WHERE type = 'L2CastleMagician';
DELETE FROM npc WHERE type = 'L2CastleWyvernManager';
DELETE FROM npc WHERE type = 'L2CastleTeleporter';
DELETE FROM npc WHERE type = 'L2CastleChamberlain';
DELETE FROM npc WHERE type = 'L2CastleDoormen';
DELETE FROM npc WHERE type = 'L2CastleBlacksmith';
DELETE FROM npc WHERE type = 'L2Artefact';
DELETE FROM npc WHERE type = 'L2ClanHallManager';
DELETE FROM npc WHERE type = 'L2ClanHallDoormen';
DELETE FROM npc WHERE type = 'L2Fisherman';
DELETE FROM npc WHERE type = 'L2Adventurer';
DELETE FROM npc WHERE type = 'L2CabaleBuffer';
DELETE FROM npc WHERE type = 'L2FestivalGuide';
DELETE FROM npc WHERE type = 'L2CabaleBuffer';
DELETE FROM npc WHERE type = 'L2DungeonGatekeeper';
DELETE FROM npc WHERE type = 'L2Auctioneer';
DELETE FROM npc WHERE type = 'L2FlyMinion';
DELETE FROM npc WHERE type = 'L2FlyRaidBoss';
DELETE FROM npc WHERE type = 'L2FlyMonster';
DELETE FROM npc WHERE type = 'L2FeedableBeast';
DELETE FROM npc WHERE type = 'L2Chest';
DELETE FROM npc WHERE type = 'L2FestivalMonster';
DELETE FROM npc WHERE type = 'L2BabyPet';
DELETE FROM npc WHERE type = 'L2Decoy';
DELETE FROM npc WHERE type = 'L2EffectPoint';
DELETE FROM npc WHERE type = 'L2ClanTrader';

This part we cut from above list:

DELETE FROM npc WHERE type = 'L2Monster';

 

At navicat we press F6(to open console) and paste text from above (where is no l2monster):

And then we press enter. We wait when it deletes all types from npc.sql except l2monster type. When it finished, refresh npc.sql and we will see only l2monster. Now we click on idTemplate and copy it all:

 

Now lets open notepad++ and paste coped text. Then we press first line, select where is red (in picture below) and press alt+C (Column Editor), at "Text to Insert" we write for egz.:"#" and press ok. Look like:

 

Now we do the same, but only on other clicked place (In red, look below) and we click alt+C (Column Editor), at "Text to Insert" we write for egz.:"*" and press ok. Look like:

Now our text looks like this:

We pressed ctrl+H (replace) and wrote in "Find what":#. And in to "Replace With":

DELETE FROM spawnlist WHERE npc_templateid = '

And press "Replace All". Now we do the same with *. We pressed ctrl+H (replace) and wrote in "Find what":*. And in to "Replace With":

';

It should look like:

And finally click replace all.

 

Finish. And we get "script to delete all mobs". Now let's press F6 (open sql console) at navicat and copy "script to delete all mobs" there, click enter. And it should delete from spawnlist.sql all mobs.

 

Now for example we want to delete all mobs from spawnlist.sql below level 20.

Now we must do everything the same.. Delete all mobs from sql except l2monster. And write into console(F6) this script:

DELETE FROM npc WHERE level > 21;

It deletes all monster bigger than 21lv+, so there will remain monster 20lv and lower. Now we do the same.. Copy culumn to notepad++ add more columns, replace them and get last script.

 

You can do that with any type of npc or their level..

 

Remember you will need delete from spawn list not only monster but and other monster npc: L2Chest, L2FeedableBeast, L2FlyMonster, L2TamedBeast, L2PenaltyMonster, L2Monster, L2FriendlyMob, L2FlyMonster, L2FlyMinion, L2Chest.

 

 

EXTRA:

 

How to change all monsters level to 80.

UPDATE npc SET level = 80 WHERE type = L2Monster

U may use any level to set or any type!

 

How to change all monsters title:

UPDATE npc SET title = yourServerName WHERE type = L2Monster
UPDATE npc SET serverSideTitle = 1 WHERE type = L2Monster

U may use any title to set or any type!

 

How to increase all monsters hp by 60%:

UPDATE npc SET hp = hp * 1.6 WHERE type = 'L2Monster';

1.6 means 60%, 1 = means nothing, 0.6 = -60% decrease. U may change type / change hp to anything (egz. p.def) etc. For example:

How to increase all guards pdef by 250%:

UPDATE npc SET pdef = pdef * 2.5 WHERE type = 'L2Guard';

It means that guards hp will be boosted by 250%...

 

 

And these basic u can do a lot of more scripts by your self...

[Kяaσh]

LOl Awesome Take +1 From Me..

[1tm]

Also everyone may use this guide for other things: merchant/multi sell, other sql tables cut/replace, list making and much more.. and do everything in few seconds using notepad++ help!  8)

 

For example how I make "forgoten scroll id list":

 

id: 10549, Forgotten Scroll - Protection of Rune (2441-1)
id: 10550, Forgotten Scroll - Protection of Elemental (2442-1)
id: 10551, Forgotten Scroll - Protection of Alignment (2443-1)
id: 10552, Forgotten Scroll - Fighter's Will (2444-1)
id: 10553, Forgotten Scroll - Archer's Will (2445-1)
id: 10554, Forgotten Scroll - Anti-Magic Armor (2446-1)
id: 10555, Forgotten Scroll - Seed of Revenge (2447-1)
id: 10556, Forgotten Scroll - Hell Scream (2448-1)
id: 10557, Forgotten Scroll - Song of Windstorm (2449-1)
id: 10558, Forgotten Scroll - Dance of Blade Storm (2450-1)
id: 10559, Forgotten Scroll - Sixth Sense (2451-1)
id: 10560, Forgotten Scroll - Expose Weak Point (2452-1)
id: 10561, Forgotten Scroll - Exciting Adventure (2453-1)
id: 10562, Forgotten Scroll - Wind Riding (2454-1)
id: 10563, Forgotten Scroll - Ghost Walking (2455-1)
id: 10564, Forgotten Scroll - Flame Hawk (2456-1)
id: 10565, Forgotten Scroll - Arrow Rain (2457-1)
id: 10566, Forgotten Scroll - Ghost Piercing (2458-1)
id: 10567, Forgotten Scroll - Dread Pool (2459-1)
id: 10568, Forgotten Scroll - Weapon Blockade (2460-1)
id: 10569, Forgotten Scroll - Force of Destruction (2461-1)
id: 10570, Forgotten Scroll - Demolition Impact (2462-1)
id: 10571, Forgotten Scroll - Golem Armor (2463-1)
id: 10572, Forgotten Scroll - Flame Armor (2464-1)
id: 10573, Forgotten Scroll - Frost Armor (2465-1)
id: 10574, Forgotten Scroll - Hurricane Armor (2466-1)
id: 10575, Forgotten Scroll - Vampiric Mist (2467-1)
id: 10576, Forgotten Scroll - Servitor Barrier (2468-1)
id: 10577, Forgotten Scroll - Excessive Loyalty (2469-1)
id: 10578, Forgotten Scroll - Mutual Response (2470-1)
id: 10579, Forgotten Scroll - Improve Combat (2471-1)
id: 10580, Forgotten Scroll - Improve Magic (2472-1)
id: 10581, Forgotten Scroll - Improve Condition (2473-1)
id: 10582, Forgotten Scroll - Improve Critical Attack (2474-1)
id: 10583, Forgotten Scroll - Improve Shield Defense (2475-1)
id: 10584, Forgotten Scroll - Improve Movement (2476-1)
id: 10585, Forgotten Scroll - Sublime Self Sacrifice (2477-1)
id: 10586, Forgotten Scroll - Blessing of Eva (2478-1)
id: 10587, Forgotten Scroll - Lord of Vampire (2479-1)
id: 10588, Forgotten Scroll - Throne Root (2480-1)
id: 10589, Forgotten Scroll - Seal of Limit (2481-1)
id: 10590, Spellbook - Summon Smart Cubic (0-0)
id: 10591, Forgotten Scroll - Spirit of Phoenix (2482-1)
id: 10592, Forgotten Scroll - Eva's Will (2483-1)
id: 10593, Forgotten Scroll - Pain of Shilen (2484-1)
id: 10594, Forgotten Scroll - Wild Shot (2488-1)
id: 10595, Forgotten Scroll - Lightning Shock (2489-1)

id: 12768, Forgotten Scroll - Evasion Counter (2526-1)
id: 12769, Forgotten Scroll - Evasion Chance (2527-1)
id: 12770, Forgotten Scroll - Evasion Haste (2528-1)
id: 12771, Forgotten Scroll - Shadow Step (2529-1)

id: 14170, Forgotten Scroll - Seed of Revenge (2803-1)
id: 14171, Forgotten Scroll - Hell Scream (2804-1)
id: 14172, Forgotten Scroll - Spirit of Phoenix (2805-1)
id: 14173, Forgotten Scroll - Eva's Will (2806-1)
id: 14174, Forgotten Scroll - Pain of Shillien (2807-1)
id: 14175, Forgotten Scroll - Exciting Adventure (2808-1)
id: 14176, Forgotten Scroll - Wind Riding (2809-1)
id: 14177, Forgotten Scroll - Ghost Walking (2810-1)
id: 14178, Forgotten Scroll - Flame Hawk (2811-1)
id: 14179, Forgotten Scroll - Arrow Rain (2812-1)
id: 14180, Forgotten Scroll - Ghost Piercing (2813-1)
id: 14181, Forgotten Scroll - Dread Pool (2814-1)
id: 14182, Forgotten Scroll - Force of Destruction (2815-1)
id: 14183, Forgotten Scroll - Demolition Impact (2816-1)
id: 14184, Forgotten Scroll - Golem Armor (2817-1)
id: 14185, Forgotten Scroll - Wild Shot (2818-1)
id: 14186, Forgotten Scroll - Lightning Shock (2819-1)
id: 14187, Forgotten Scroll - Flame Armor (2820-1)
id: 14188, Forgotten Scroll - Frost Armor (2821-1)
id: 14189, Forgotten Scroll - Hurricane Armor (2822-1)
id: 14190, Forgotten Scroll - Vampiric Mist (2823-1)
id: 14191, Forgotten Scroll - Meteor (2824-1)
id: 14192, Forgotten Scroll - Star Fall (2825-1)
id: 14193, Forgotten Scroll - Sublime Self Sacrifice (2826-1)
id: 14194, Forgotten Scroll - Blessing of Eva (2827-1)
id: 14195, Forgotten Scroll - Lord of Vampire (2828-1)
id: 14196, Forgotten Scroll - Throne Route (2829-1)
id: 14197, Forgotten Scroll - Seal of Limit (2830-1)
id: 14198, Forgotten Scroll - Song of Purification (2652-1)
id: 14199, Forgotten Scroll - Dance of Berserker (2653-1)
id: 14200, Forgotten Scroll - Summon Imperial Phoenix (2650-1)
id: 14201, Forgotten Scroll - Maximum Focus Force (2655-1)
id: 14202, Forgotten Scroll - Maximum Focus Sonic (2656-1)
id: 14203, Forgotten Scroll - Weapon Blockade (2831-1)
id: 14204, Forgotten Scroll - Spirit of the Cat (2667-1)
id: 14205, Forgotten Scroll - Spirit of the Unicorn (2669-1)
id: 14206, Forgotten Scroll - Spirit of the Phantom (2668-1)
id: 14207, Forgotten Scroll - Deflect Magic (2651-1)
id: 14208, Forgotten Scroll - Final Secret (2654-1)
id: 14209, Forgotten Scroll - Hide (2657-1)
id: 14210, Forgotten Scroll - Dual Dagger Mastery (2658-1)
id: 14211, Forgotten Scroll - Seven Arrow (2659-1)
id: 14212, Forgotten Scroll - Enlightenment - Wizard (2660-1)
id: 14213, Forgotten Scroll - Enlightenment - Healer (2661-1)
id: 14214, Forgotten Scroll - Chant of Movement (2662-1)
id: 14215, Forgotten Scroll - Combat of Paagrio (2663-1)
id: 14216, Forgotten Scroll - Critical of Paagrio (2664-1)
id: 14217, Forgotten Scroll - Condition of Paagrio (2665-1)
id: 14218, Forgotten Scroll - Duel Blow (2666-1)
id: 14219, Forgotten Scroll - Magician's Will (2780-1)
id: 14220, Forgotten Scroll - Silent Mind (2781-1)
id: 14221, Forgotten Scroll - Turn Stone (2782-1)
id: 14222, Forgotten Scroll - Lucky Strike (2783-1)
id: 14223, Forgotten Scroll - Eye for Eye (2784-1)
id: 14224, Forgotten Scroll - Curse of Desparion (2785-1)
id: 14225, Forgotten Scroll - Counter Critical (2786-1)
id: 14226, Forgotten Scroll - Onslaught of Pa'agrio (2787-1)
id: 14227, Forgotten Scroll - Great Fury (2788-1)

 

1. First of all I go to l2jserver dp svn, then I open etcitem.sql and I start search (ctrl+f): "Forgotten Scroll", and then copy all lines with forgotten scrolls. For example:

(12768,'Forgotten Scroll - Evasion Counter','false','spellbook',120,'stackable','paper','none',-1,-1,0,0,'true','true','true','true','true','ItemSkills','2526-1;'),
(12769,'Forgotten Scroll - Evasion Chance','false','spellbook',120,'stackable','paper','none',-1,-1,0,0,'true','true','true','true','true','ItemSkills','2527-1;'),
(12770,'Forgotten Scroll - Evasion Haste','false','spellbook',120,'stackable','paper','none',-1,-1,0,0,'true','true','true','true','true','ItemSkills','2528-1;'),
(12771,'Forgotten Scroll - Shadow Step','false','spellbook',120,'stackable','paper','none',-1,-1,0,0,'true','true','true','true','true','ItemSkills','2529-1;'),

 

2. Then I paste lines to notepad++ and press ctrl+h (replace), to "find what" I write:

','false','spellbook',120,'stackable','paper','none',-1,-1,0,0,'true','true','true','true','true','ItemSkills','

To "Replace with":

  [sb needed for this skill:

And click "replace all" and text looks like:

(12768,'Forgotten Scroll - Evasion Counter [sb needed for this skill: 2526-1;'),
(12769,'Forgotten Scroll - Evasion Chance [sb needed for this skill: 2527-1;'),
(12770,'Forgotten Scroll - Evasion Haste [sb needed for this skill: 2528-1;'),
(12771,'Forgotten Scroll - Shadow Step [sb needed for this skill: 2529-1;'),

 

3. Then I do egzacly the same.. replace what I want with some thing else.

 

Replaced this [(] for [id: ]

Replaced this [,'] for [, ]

Replaced this [;'),] for [].]

 

And final "Forgotten Scroll id list" looks like:

^{Id: 12768, Forgotten Scroll - Evasion Counter [sb needed for this skill: 2526-1].}

^{Id: 12769, Forgotten Scroll - Evasion Chance [sb needed for this skill: 2527-1].}

^{Id: 12770, Forgotten Scroll - Evasion Haste [sb needed for this skill: 2528-1].}

^{Id: 12771, Forgotten Scroll - Shadow Step [sb needed for this skill: 2529-1].}

[SySt3MGaM3RFr3aKs]

hmm Very Interesting Guide ! Keep Sharing Mate (L) wich means i love yo :)

[WickedPwn]

Dude very thanks ... i was thinking how to delete all monsters from my spawn list really helped me :)

P.S. i get error

ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '35431' at line 1

[1tm]

Dude very thanks ... i was thinking how to delete all monsters from my spawn list really helped me :)

P.S. i get error

ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '35431' at line 1

At which part?

[Levi4than]

Great share bro...Keep going!!

[~GeFriDo~]

Thanks man great guide and work ;p!! ;)