To Private Server administrators/DBA

-- 1. First: Put Character Name in.
Declare @Name as varchar(255)
Set @Name='name here' --Place Character name of the person here.

-- This searches for their Current Family and Job (and to see if it is the right person) when script is Executed.
--(Theres after change checkup too, to see if the Family and Job changed to right ones.)
Select UserID, UserUID, CharID, CharName, Del, Family, Job, Level, LoginStatus From PS_GameData.dbo.Chars Where CharName=@Name and Del='0'

-- 2. Second: Put the New Family Value they want.
Update PS_GameData.dbo.Chars
Set Family='0' -- Warning:If runned empty then changes value to 0.
-- For Family: 0 Human / 1 Elves / 2 Vail / 3 Nordein
Where CharName=@Name and Del='0'

-- 3. Third: Put the New Job Value they want.
Update PS_GameData.dbo.Chars
Set Job='5' -- Warning:If runned empty then changes value to 0.
-- For Job: 0 Warrior-Fighter / 1 Guardian-Defender / 2 Assassin-Ranger
-- 3 Hunter-Archer / 4 Pagan-Mage / 5 Oracle-Priest
Where CharName=@Name and Del='0'

-- This is the After change checkup to compare Old Family and Job to New values when script is Executed.
Select UserID, UserUID, CharID, CharName, Del, Family, Job, Level, LoginStatus From PS_GameData.dbo.Chars Where CharName=@Name and Del='0'

--When Script is Executed, This will show items they had in Inventory before change.
--(Theres same chekup after Script too to get new Values and to compare them 2 to look for possible errors.)
Select CharID, ci.Type, ci.TypeID, i.ItemName, Bag, ci.Slot from PS_GameData.dbo.CharItems ci
inner join PS_GameDefs.dbo.Items i
on ci.Type=i.Type and ci.TypeID = i.TypeID
WHERE
CharID in ( SELECT
CharID
FROM
PS_GameData.dbo.Chars
Where
CharName=@Name
and Del='0')

---Löscht gedubte und die Originale---


---Character-Items-Löschen---
USE PS_GameData
DELETE FROM dbo.CharItems
WHERE EXISTS (SELECT ItemUID FROM dbo.Charitems dub WHERE dbo.Charitems.ItemUID = dub.ItemUID and dbo.Charitems.CharID <> dub.CharID);

---Warenlager-Items-Löschen---
USE PS_GameData
DELETE FROM dbo.UserStoredItems
WHERE EXISTS (SELECT ItemUID FROM dbo.UserStoredItems dub WHERE dbo.UserStoredItems.ItemUID = dub.ItemUID and dbo.UserStoredItems.UserUID <> dub.UserUID);

---Auctionshaus-Items-Löschen---
USE PS_GameData
DELETE FROM dbo.MarketItems
WHERE EXISTS (SELECT ItemUID FROM dbo.MarketItems dub WHERE dbo.MarketItems.ItemUID = dub.ItemUID and dbo.MarketItems.MarketID <> dub.MarketID);

---Gildenlager-Items-Löschen---
USE PS_GameData
DELETE FROM dbo.GuildStoredItems
WHERE EXISTS (SELECT ItemUID FROM dbo.GuildStoredItems dub WHERE dbo.GuildStoredItems.ItemUID = dub.ItemUID and dbo.GuildStoredItems.GuildID <> dub.GuildID);

UPDATE [PS_GameData].[dbo].[Chars]

SET CharName= 'newname'
WHERE CharName= 'oldname'

Declare @Name as varchar(255)
Set @Name='xxx' --Place Character name of the person here.

SELECT
ci.CharID
,ci.Type
,ci.TypeID
,i.ItemName
,ci.Bag
,ci.Slot
,ci.ItemUID
,ci.Craftname
,ci.Gem1
,ci.Gem2
,ci.Gem3
,ci.Gem4
,ci.Gem5
,ci.Gem6
,ci.Quality
,ci.Count
,ci.Del
From PS_GameData.dbo.CharItems ci
inner join PS_GameDefs.dbo.Items i
on ci.Type=i.Type and ci.TypeID = i.TypeID
WHERE
CharID in ( SELECT
CharID
FROM
PS_GameData.dbo.Chars
Where
CharName=@Name
and Del='0')

/* Clookup script for how a ppl link with special lapis. change it so it works for your db */
SELECT *
into #SpecLapis
FROM [PS_GameDefs].[dbo].[Items]
where [Type] = 30 and TypeID >= 233 and TypeID <= 250 and TypeID not in (240,241,242,243)

SELECT
UserID
,CharName
,i.ItemName
,l1.ItemName as 'Lapis1'
,L2.ItemName as 'Lapis2'
,L3.ItemName as 'Lapis3'
,L4.ItemName as 'Lapis4'
,L5.ItemName as 'Lapis5'
,L6.ItemName as 'Lapis6'
,SUBSTRING(Craftname,1,2) as 'Str'
,SUBSTRING(Craftname,3,2) as 'Dex'
,SUBSTRING(Craftname,5,2) as 'Rec'
,SUBSTRING(Craftname,7,2) as 'Int'
,SUBSTRING(Craftname,9,2) as 'Wis'
,SUBSTRING(Craftname,11,2) as 'Luc'
,SUBSTRING(Craftname,13,2)+'00' as 'HP'
,SUBSTRING(Craftname,15,2)+'00' as 'MP'
,SUBSTRING(Craftname,17,2)+'00' as 'SP'
,case when cast(SUBSTRING(Craftname,19,2) as int) >= 50
then cast(SUBSTRING(Craftname,19,2) as int) - 50
else cast(SUBSTRING(Craftname,19,2) as int)
end as 'Enchant'
-- ,ci.* ,i.* ,c.*
FROM [PS_GameData].[dbo].[CharItems] ci with (nolock)
inner join [PS_GameDefs].[dbo].[Items] i with (nolock)
on i.ItemID = ci.ItemID
inner join [PS_GameData].[dbo].[Chars] c with (nolock)
on c.CharID = ci.CharID
left join [PS_GameDefs].[dbo].[Items] L1 with (nolock)
on L1.TypeID = ci.Gem1
and L1.Type = 30
left join [PS_GameDefs].[dbo].[Items] L2 with (nolock)
on L2.TypeID = ci.Gem2
and L2.Type = 30
left join [PS_GameDefs].[dbo].[Items] L3 with (nolock)
on L3.TypeID = ci.Gem3
and L3.Type = 30
left join [PS_GameDefs].[dbo].[Items] L4 with (nolock)
on L4.TypeID = ci.Gem4
and L4.Type = 30
left join [PS_GameDefs].[dbo].[Items] L5 with (nolock)
on L5.TypeID = ci.Gem5
and L5.Type = 30
left join [PS_GameDefs].[dbo].[Items] L6 with (nolock)
on L6.TypeID = ci.Gem6
and L6.Type = 30
where
Gem1 in ( select TypeID from #SpecLapis )
or Gem2 in ( select TypeID from #SpecLapis )
or Gem3 in ( select TypeID from #SpecLapis )
or Gem4 in ( select TypeID from #SpecLapis )
or Gem5 in ( select TypeID from #SpecLapis )
or Gem6 in ( select TypeID from #SpecLapis )
order by
UserID, CharName


drop table #SpecLapis

UPDATE PS_UserData.dbo.Users_Master set Status = -5 WHERE UserIp = 'ip here'