To Private Server administrators/DBA

[JohnHeatz]

Hello community,

This post is to gather some information that I am needing to know right now, the question is simple, can you -administrators/GM/database administrators- let me know which are the most used scripts you all use on your daily tasks on the database?

With this I mean, for example that you say "Ban an account/IP ban someone", this with all the ones you use the most.

Hopefully I will get all the information I am needed by now, and in the case I need some further information I will let you all know.

Gathered so far:

Accounts	Characters	Items	Guilds	Mobs	Logs
Give Points	Stat Pad check
Look for account	GM Enchants
Look for IP	GM Links
Password Change	Look for Toon
	Character Resurrection
	Remove Guild Penalty
	Change Name

[thetruestarr1337]

Every 12h Database Backup
Everyday Stadpadder Search
and DP Give Querie

Those 3 Scripts im using mostly

[[Boss]FemmeFatale]

GM Enchants

SELECT TOP (20000000) CharID, ItemID, Type, TypeID, Bag, Craftname
FROM CharItems
WHERE (CharID = ???) AND (Bag = ?)

OR

SELECT TOP (20000000) UserUID, ItemID, Type, TypeID, Slot, Craftname, Del
FROM UserStoredItems
WHERE (UserUID = ???) AND (Slot < ?)<------ i have used the < sign as we do our Enchants from WH with Forum posts

USE PS_UserData;
UPDATE Users_Master
SET Point = (Point + ?)
WHERE UserID = '?'

the above can have Gems included too if you do GM links

these are probably our most used scripts.

[STIV123]

SELECT * FROM PS_Gamedata.dbo.Chars WHERE CharName='charname'

SELECT * FROM PS_UserData.dbo.Users_Master WHERE UserID='UserID'

SELECT * FROM PS_GameLog.dbo.actionlog WHERE CharName='charname'


SELECT * FROM PS_UserData.dbo.Users_Master WHERE UserIp='IPHere'

This what i use when i server restart: UPDATE PS_UserData.dbo.Users_Master SET Leave = 0

I use this scripts most.

[JohnHeatz]

I don't need the script itself, just what they do, still wouldn't say no if you post the full script though.

Thanks for the information so far, hopefully I will be able to gather the information I am needing soon.

Update

I have added a table containing what I see could be more used from all the information given, will be updating every time someone provides any query/script

[JohnHeatz]

Wouldn't like to not get any more replies in here, if there is any script/query that you think is useful and something that is used on "daily tasks" let me know

[_-Phoenix-_]

there is also the toon res,

[STIV123]

Remove Guild Penalty
Password Change
Name Change
Script for deleteing items from invertory, WH, GWH. ^

[JohnHeatz]

Haven't thought about deleting items from inventory, how often do you use that script?

[[Admin]Snuggle]

This thread needs a...
-Snuggle Bump- >.>

(hell yea I'm naming my own bumps after myself..got an issue with it?)

[JohnHeatz]

Let's bump this thread, more answers would be greatly appreciated.

Let's think about every important script you all would like to have ready to be used for any circumstance; thinking about:

• Accounts
• Characters
• Guilds
• Mobs
• Items
• Logs

Let's see if I can get some more replies in here

[[Admin]Snuggle]

Checking for corrupt GMs would be a good one. o.o

[TeddyBear94]

char change

 Spoiler

-- 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')

deleating duped items with originals

 Spoiler

---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);

tag adding

 Spoiler

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

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

search charname with inventory infos

 Spoiler

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')

having lapis linked wich got a special ID ( this lapis are on my server 31 + and i use that to check if some1 used a prog to link them in a lower gear)

 Spoiler

/* 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

ip bann

 Spoiler

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

that are my most used quiery´s

[RebeccaBlack]

I have a function I use (named "BackupDB") that will backup any and all databases when executed (apart from the sys databases). Quite useful for a python script I threw together that backs everything up (including server files), makes a RAR archive and uploads it.

A simple query to check GM commands (bit different since I log them to a different table, same as kills)

I can't think of others offhand, maybe later.

[STIV123]

There are also:

Script for checking drops: Example, you wanna check what drops under grade 02, and runnin the script will show you what all Items drop from It. It's what I use sometimes to search the not used ones.


Script for checking logs, action type.

Haven't thought about deleting items from inventory, how often do you use that script?

Just seen this that you asked me before, lol. Anyway, well not really often, but let's say some of your staff quits or you fire him, you would want to remove he's name changes stones or messages to server, so you would just type ItemID and CharID, execute and done. that was an example.

There Is script for rerroll as well.

SELECT CharName, ActionTime, Text1
FROM PS_GameLog.dbo.ActionLog
WHERE ActionType = 107 and charname = 'charname' This would be IP checker..