Release] Queries – Unique / Mob Add/Delete with One Click - TopS4A

👋 Welcome everyone!

Today I'm bringing you a powerful SQL tool — with one click, you can delete all your unique monsters, and with another, spawn them again instantly at any position!

Add or delete any unique monster instantly with no hassle.

• ✔ Deletes the unique from: Tab_RefTactics, Tab_RefHive, and Tab_RefNest
• ✔ Clean and correct deletion order
• ✔ Displays full info (Nest ID, Hive ID, Tactics ID)
• ✔ Error message if codename doesn't exist

• ✔ Spawns the unique at any character’s current position
• ✔ Automatically fetches (X, Y, Z) coordinates
• ✔ Generates new IDs for Nest, Hive, and Tactics
• ✔ Full detailed spawn report

---

• ✔ No manual ID handling required
• ✔ Built-in character name validation
• ✔ Perfect for events, global uniques, and scheduled spawns

➕ Add Unique

USE SRO_VT_SHARD
GO

-- Script Author: Ahmed Nesta
-- Purpose: Insert a monster at the character's current location using its codename
-- Features: Dynamic ID generation, character position fetching, and safe multi-table insertion
-- Script Name: Insert_Mob_By_CodeName_With_Char_Position.sql

-- Configuration variables
DECLARE @UNIQUE VARCHAR(129) = 'MOB_JUPITER_YUNO'
DECLARE @CHARNAME VARCHAR(64) = 'nesta'
DECLARE @mindealy INT = 60        -- Minimum delay in seconds before the monster respawns
DECLARE @maxdelay INT = 120       -- Maximum delay in seconds before the monster respawns
DECLARE @INITRADIUS INT = 100     -- Initial spawn radius: how far from the center the monster can spawn
DECLARE @RADIUS INT = 500         -- Movement radius: how far the monster can roam from the spawn point

-- Validate character existence
IF NOT EXISTS (SELECT 1 FROM _Char WHERE CharName16 = @CHARNAME)
BEGIN
    RAISERROR('Character %s does not exist in _Char.', 16, 1, @CHARNAME)
    RETURN
END

-- Generate new unique IDs
DECLARE @MAXNEST INT = (SELECT ISNULL(MAX(dwNestID), 0) + 1 FROM Tab_RefNest)
DECLARE @MAXHIVE INT = (SELECT ISNULL(MAX(dwHiveID), 0) + 1 FROM Tab_RefHive)
DECLARE @MAXTACTICS INT = (SELECT ISNULL(MAX(dwTacticsID), 0) + 1 FROM Tab_RefTactics)

-- Get mob ID by codename
DECLARE @MOBID INT = (SELECT ID FROM _RefObjCommon WHERE CodeName128 = @UNIQUE)

-- Insert into Tab_RefTactics
SET IDENTITY_INSERT Tab_RefTactics ON
INSERT INTO Tab_RefTactics (
    dwTacticsID, dwObjID, btAIQoS, nMaxStamina, btMaxStaminaVariance, nSightRange,
    btAggressType, AggressData, btChangeTarget, btHelpRequestTo, btHelpResponseTo,
    btBattleStyle, BattleStyleData, btDiversionBasis, DiversionBasisData1, DiversionBasisData2,
    DiversionBasisData3, DiversionBasisData4, DiversionBasisData5, DiversionBasisData6,
    DiversionBasisData7, DiversionBasisData8, btDiversionKeepBasis, DiversionKeepBasisData1,
    DiversionKeepBasisData2, DiversionKeepBasisData3, DiversionKeepBasisData4,
    DiversionKeepBasisData5, DiversionKeepBasisData6, DiversionKeepBasisData7,
    DiversionKeepBasisData8, btKeepDistance, KeepDistanceData, btTraceType, btTraceBoundary,
    TraceData, btHomingType, HomingData, btAggressTypeOnHoming, btFleeType,
    dwChampionTacticsID, AdditionOptionFlag, szDescString128
) VALUES (
    @MAXTACTICS, @MOBID, 0, 500, 50, 200, 0, 0, 2, 2, 2, 0, 0, 5,
    0, 0, 0, 0, 0, 30, 0, 0, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0,
    1, 255, 0, 0, 100, 2, 0, 0, 112, 0, @UNIQUE
)
SET IDENTITY_INSERT Tab_RefTactics OFF

-- Insert into Tab_RefHive
SET IDENTITY_INSERT Tab_RefHive ON
INSERT INTO Tab_RefHive (
    dwHiveID, btKeepMonsterCountType, dwOverwriteMaxTotalCount, fMonsterCountPerPC,
    dwSpawnSpeedIncreaseRate, dwMaxIncreaseRate, btFlag, GameWorldID,
    HatchObjType, szDescString128
) VALUES (
    @MAXHIVE, 0, 1, 0, 0, 0, 0, 1, 1, @UNIQUE
)
SET IDENTITY_INSERT Tab_RefHive OFF

-- Get character position
DECLARE @REGION INT = (SELECT LatestRegion FROM _Char WHERE CharName16 = @CHARNAME)
DECLARE @POSX INT = (SELECT POSX FROM _Char WHERE CharName16 = @CHARNAME)
DECLARE @POSY INT = (SELECT POSY FROM _Char WHERE CharName16 = @CHARNAME)
DECLARE @POSZ INT = (SELECT POSZ FROM _Char WHERE CharName16 = @CHARNAME)

-- Insert into Tab_RefNest
SET IDENTITY_INSERT Tab_RefNest ON
INSERT INTO Tab_RefNest (
    dwNestID, dwHiveID, dwTacticsID, nRegionDBID,
    fLocalPosX, fLocalPosY, fLocalPosZ, wInitialDir,
    nRadius, nGenerateRadius, nChampionGenPercentage,
    dwDelayTimeMin, dwDelayTimeMax, dwMaxTotalCount,
    btFlag, btRespawn, btType
) VALUES (
    @MAXNEST, @MAXHIVE, @MAXTACTICS, @REGION,
    @POSX, @POSY, @POSZ, 0,
    @RADIUS, @INITRADIUS, 0,
    @mindealy, @maxdelay, 1,
    0, 1, 0
)
SET IDENTITY_INSERT Tab_RefNest OFF

-- Show all recent rows added to Tab_RefNest
PRINT 'Inserted rows in Tab_RefNest:'
SELECT 
    dwNestID, dwHiveID, dwTacticsID, nRegionDBID, fLocalPosX, fLocalPosY, fLocalPosZ, 
    nRadius, nGenerateRadius, dwDelayTimeMin, dwDelayTimeMax, dwMaxTotalCount, btFlag, btRespawn, btType
FROM Tab_RefNest
WHERE dwNestID >= @MAXNEST

-- Show character info below inserted rows
PRINT 'Character info:'
SELECT 
    CharName16 AS CharacterName,
    LatestRegion AS Region,
    POSX AS PositionX,
    POSY AS PositionY,
    POSZ AS PositionZ
FROM _Char
WHERE CharName16 = @CHARNAME

-- Confirm success
PRINT 'All records inserted and displayed successfully.'

  

➖ Delete Unique

USE SRO_VT_SHARD
GO

-- ========================================
-- Script by: Ahmed Nesta
-- Features:
-- 1. Deletes all entries for a specific mob from Tab_RefTactics, Tab_RefHive, and Tab_RefNest.
-- 2. Automatically handles deletion in the correct order to maintain referential integrity.
-- 3. Outputs details of each deleted entry for transparency and logging.
-- ========================================

-- Configuration: set your unique mob codename
DECLARE @UniqueCode VARCHAR(129) = 'MOB_JUPITER_YUNO'

-- Loop variables
DECLARE @TacticsID INT, @HiveID INT, @NestID INT

-- Cursor-style loop to delete all matching entries
WHILE EXISTS (
    SELECT 1
    FROM Tab_RefTactics T
    JOIN Tab_RefHive H ON H.szDescString128 = T.szDescString128
    JOIN Tab_RefNest N ON N.dwTacticsID = T.dwTacticsID AND N.dwHiveID = H.dwHiveID
    WHERE T.szDescString128 = @UniqueCode
)
BEGIN
    -- Fetch top record matching the codename
    SELECT TOP 1
        @TacticsID = T.dwTacticsID,
        @HiveID = H.dwHiveID,
        @NestID = N.dwNestID
    FROM Tab_RefTactics T
    JOIN Tab_RefHive H ON H.szDescString128 = T.szDescString128
    JOIN Tab_RefNest N ON N.dwTacticsID = T.dwTacticsID AND N.dwHiveID = H.dwHiveID
    WHERE T.szDescString128 = @UniqueCode

    -- Delete in order: Nest -> Hive -> Tactics
    DELETE FROM Tab_RefNest WHERE dwNestID = @NestID
    DELETE FROM Tab_RefHive WHERE dwHiveID = @HiveID
    DELETE FROM Tab_RefTactics WHERE dwTacticsID = @TacticsID

    -- Output deleted info
    PRINT 'Deleted Record Set:'
    PRINT '- Nest ID: ' + CAST(@NestID AS VARCHAR)
    PRINT '- Hive ID: ' + CAST(@HiveID AS VARCHAR)
    PRINT '- Tactics ID: ' + CAST(@TacticsID AS VARCHAR)
END

PRINT 'All matching mobs with codename "' + @UniqueCode + '" were deleted.'

-- ========================================
-- Script Created by Ahmed Nesta
-- Silkroad SQL Development
-- ========================================