Go Back   EQEmulator Home > EQEmulator Forums > Development > Development::Database/World Building
Reload this Page Project - NPC Wipe

Development::Database/World Building World Building forum, dedicated to the EQEmu MySQL Database. Post partial/complete databases for spawns, items, etc.

Reply
 
Thread Tools Display Modes
  #1  
Old 08-16-2021, 10:04 AM
Huppy's Avatar
Huppy
Demi-God
  
Join Date: Oct 2010
Posts: 1,332
Default Project - NPC Wipe
I created (what I call) a "template database" for the start of a long, journey of redoing every zone on the server with custom NPC spawns, quests, etc. I have totally wiped any and all entries in the spawn tables, loot tables, grid entries, merchant lists, etc. May take 12-18 months to make progress on it, but i am in no rush. Just going to pick away at it, when I need a break from other things.

I put together an sql query, if anyone else is interested in doing this, but BE FOREWARNED - BACK UP YOUR DATABASE FIRST !!! This query will leave your server with NO NPC's - SERVER WIDE. The NPC's are still in the database, but all spawn tables/entries will be gone.

Cautionary note - This is based on work with the current db version 9167. In the future, as the PEQ team and Devs do updates, in some cases, it could render this query troublesome for your database. Added/deleted/altered tables and columns are common in many updates.

Copy and paste this into a query and run it.

Code:
/* WARNING - This will delete ALL entries related to NPC spawns, grids, loot tables - SERVER WIDE */

DROP TABLE IF EXISTS `grid_entries`;
CREATE TABLE `grid_entries`  (
  `gridid` int(10) NOT NULL DEFAULT 0,
  `zoneid` int(10) NOT NULL DEFAULT 0,
  `number` int(10) NOT NULL DEFAULT 0,
  `x` float NOT NULL DEFAULT 0,
  `y` float NOT NULL DEFAULT 0,
  `z` float NOT NULL DEFAULT 0,
  `heading` float NOT NULL DEFAULT 0,
  `pause` int(10) NOT NULL DEFAULT 0,
  `centerpoint` tinyint(4) NOT NULL DEFAULT 0,
  PRIMARY KEY (`zoneid`, `gridid`, `number`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS `grid`;
CREATE TABLE `grid`  (
  `id` int(10) NOT NULL DEFAULT 0,
  `zoneid` int(10) NOT NULL DEFAULT 0,
  `type` int(10) NOT NULL DEFAULT 0,
  `type2` int(10) NOT NULL DEFAULT 0,
  PRIMARY KEY (`zoneid`, `id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS `lootdrop`;
CREATE TABLE `lootdrop`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
  `min_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
  `max_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
  `content_flags` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `content_flags_disabled` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 178218 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS `lootdrop_entries`;
CREATE TABLE `lootdrop_entries`  (
  `lootdrop_id` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `item_id` int(11) NOT NULL DEFAULT 0,
  `item_charges` smallint(2) UNSIGNED NOT NULL DEFAULT 1,
  `equip_item` tinyint(2) UNSIGNED NOT NULL DEFAULT 0,
  `chance` float NOT NULL DEFAULT 1,
  `disabled_chance` float NOT NULL DEFAULT 0,
  `trivial_min_level` smallint(5) UNSIGNED NOT NULL DEFAULT 0,
  `trivial_max_level` smallint(5) UNSIGNED NOT NULL DEFAULT 0,
  `multiplier` tinyint(2) UNSIGNED NOT NULL DEFAULT 1,
  `npc_min_level` smallint(5) UNSIGNED NOT NULL DEFAULT 0,
  `npc_max_level` smallint(5) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`lootdrop_id`, `item_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS `loottable`;
CREATE TABLE `loottable`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
  `mincash` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `maxcash` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `avgcoin` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `done` tinyint(3) NOT NULL DEFAULT 0,
  `min_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
  `max_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
  `content_flags` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `content_flags_disabled` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 110705 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS `loottable`;
CREATE TABLE `loottable`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
  `mincash` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `maxcash` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `avgcoin` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `done` tinyint(3) NOT NULL DEFAULT 0,
  `min_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
  `max_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
  `content_flags` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `content_flags_disabled` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 110705 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS `loottable_entries`;
CREATE TABLE `loottable_entries`  (
  `loottable_id` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `lootdrop_id` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `multiplier` tinyint(2) UNSIGNED NOT NULL DEFAULT 1,
  `droplimit` tinyint(2) UNSIGNED NOT NULL DEFAULT 0,
  `mindrop` tinyint(2) UNSIGNED NOT NULL DEFAULT 0,
  `probability` float NOT NULL DEFAULT 100,
  PRIMARY KEY (`loottable_id`, `lootdrop_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS `merchantlist`;
CREATE TABLE `merchantlist`  (
  `merchantid` int(11) NOT NULL DEFAULT 0,
  `slot` int(11) NOT NULL DEFAULT 0,
  `item` int(11) NOT NULL DEFAULT 0,
  `faction_required` smallint(6) NOT NULL DEFAULT -100,
  `level_required` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `alt_currency_cost` smallint(5) UNSIGNED NOT NULL DEFAULT 0,
  `classes_required` int(11) NOT NULL DEFAULT 65535,
  `probability` int(3) NOT NULL DEFAULT 100,
  `min_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
  `max_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
  `content_flags` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `content_flags_disabled` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`merchantid`, `slot`) USING BTREE,
  UNIQUE INDEX `merchantid`(`merchantid`, `item`) USING BTREE,
  INDEX `item`(`item`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS `merchantlist_temp`;
CREATE TABLE `merchantlist_temp`  (
  `npcid` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `slot` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `itemid` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `charges` int(10) UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (`npcid`, `slot`) USING BTREE,
  INDEX `npcid_2`(`npcid`, `itemid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

SET FOREIGN_KEY_CHECKS = 1;

DROP TABLE IF EXISTS `respawn_times`;
CREATE TABLE `respawn_times`  (
  `id` int(11) NOT NULL DEFAULT 0,
  `start` int(11) NOT NULL DEFAULT 0,
  `duration` int(11) NOT NULL DEFAULT 0,
  `instance_id` smallint(6) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`, `instance_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS `spawn_condition_values`;
CREATE TABLE `spawn_condition_values`  (
  `id` int(10) UNSIGNED NOT NULL,
  `value` tinyint(3) UNSIGNED NULL DEFAULT NULL,
  `zone` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `instance_id` int(10) UNSIGNED NOT NULL,
  UNIQUE INDEX `instance`(`id`, `instance_id`, `zone`) USING BTREE,
  INDEX `zoneinstance`(`zone`, `instance_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS `spawn_conditions`;
CREATE TABLE `spawn_conditions`  (
  `zone` varchar(32) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
  `id` mediumint(8) UNSIGNED NOT NULL DEFAULT 1,
  `value` mediumint(9) NOT NULL DEFAULT 0,
  `onchange` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`zone`, `id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS `spawn_events`;
CREATE TABLE `spawn_events`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `zone` varchar(32) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `cond_id` mediumint(8) UNSIGNED NOT NULL DEFAULT 0,
  `name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
  `period` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `next_minute` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `next_hour` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `next_day` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `next_month` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `next_year` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `enabled` tinyint(4) NOT NULL DEFAULT 1,
  `action` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `argument` mediumint(9) NOT NULL DEFAULT 0,
  `strict` tinyint(4) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 84 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS `spawn2`;
CREATE TABLE `spawn2`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `spawngroupID` int(11) NOT NULL DEFAULT 0,
  `zone` varchar(32) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `version` smallint(5) NOT NULL DEFAULT 0,
  `x` float(14, 6) NOT NULL DEFAULT 0.000000,
  `y` float(14, 6) NOT NULL DEFAULT 0.000000,
  `z` float(14, 6) NOT NULL DEFAULT 0.000000,
  `heading` float(14, 6) NOT NULL DEFAULT 0.000000,
  `respawntime` int(11) NOT NULL DEFAULT 0,
  `variance` int(11) NOT NULL DEFAULT 0,
  `pathgrid` int(10) NOT NULL DEFAULT 0,
  `path_when_zone_idle` tinyint(1) NOT NULL DEFAULT 0,
  `_condition` mediumint(8) UNSIGNED NOT NULL DEFAULT 0,
  `cond_value` mediumint(9) NOT NULL DEFAULT 1,
  `enabled` tinyint(3) UNSIGNED NOT NULL DEFAULT 1,
  `animation` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `min_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
  `max_expansion` tinyint(4) UNSIGNED NOT NULL DEFAULT 0,
  `content_flags` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `content_flags_disabled` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `ZoneGroup`(`zone`) USING BTREE,
  INDEX `spawn2_spawngroupid_idx`(`spawngroupID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 264328 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS `spawnentry`;
CREATE TABLE `spawnentry`  (
  `spawngroupID` int(11) NOT NULL DEFAULT 0,
  `npcID` int(11) NOT NULL DEFAULT 0,
  `chance` smallint(4) NOT NULL DEFAULT 0,
  `condition_value_filter` mediumint(9) NOT NULL DEFAULT 1,
  PRIMARY KEY (`spawngroupID`, `npcID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS `spawngroup`;
CREATE TABLE `spawngroup`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
  `spawn_limit` tinyint(4) NOT NULL DEFAULT 0,
  `dist` float NOT NULL DEFAULT 0,
  `max_x` float NOT NULL DEFAULT 0,
  `min_x` float NOT NULL DEFAULT 0,
  `max_y` float NOT NULL DEFAULT 0,
  `min_y` float NOT NULL DEFAULT 0,
  `delay` int(11) NOT NULL DEFAULT 45000,
  `mindelay` int(11) NOT NULL DEFAULT 15000,
  `despawn` tinyint(3) NOT NULL DEFAULT 0,
  `despawn_timer` int(11) NOT NULL DEFAULT 100,
  `wp_spawns` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 287829 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
__________________
Hanging out at Antonica.World
Reply With Quote
Reply

« Previous Thread | Next Thread »

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

   

All times are GMT -4. The time now is 05:30 PM.

EQEmulator - Archive - Top

 

Everquest is a registered trademark of Daybreak Game Company LLC.
EQEmulator is not associated or affiliated in any way with Daybreak Game Company LLC.
Except where otherwise noted, this site is licensed under a Creative Commons License.
       
Powered by vBulletin®, Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Template by Bluepearl Design and vBulletin Templates - Ver3.3