To Private Server administrators/DBA

Really awesome to see so many replies, thank you for the information, if you all can think of anything else, feel free to post in here

I've built some stored procedures for getting drops, grades, items and such.
Mainly for quick lookups but I'll post anyway:
The stored procs are:

ShowGrade ( usage: exec ShowGrade 999 )

ShowDropsByGrade ( usage: exec ShowDropsByGrade 100 )
ShowDropsByItemName ( usage: exec ShowDropsByItemName 'itemname' )
The stored proc uses "like" so you can include wildcard searches, 'item%' or '%name' or '%mnam%'

ShowDropsByMobName ( usage: exec ShowDropsByMobName 'Mobname' )
The stored proc uses "like" so you can include wildcard searches, 'mob%' or '%name' or '%obnam%'

The other cool thing is that you can use these to setup a searchable drop list on your site, AND it's always right and updated, no need to update your droplist as it gets it right from your DB. Now you may not want to return percentages, just to keep them on their toes ;)

P.S. it's nice to see ppl sharing info on here again, now if I could just get my hands on the ep6 items table... :D

Ich benutze Oft!

-----AP adden-----

USE PS_GameData
DECLARE
@CharName varchar(30),
@UserUID int,
@UserID varchar(18),
@Point int,
@Points int,
@Point2 int

--Change "Char Name" to the name of the Character who you're giving DP to
SET @CharName = '*********'
--Change "PointsToGive" to the amount of DP you want to give to that Character
SET @Point = '4000'

SELECT @UserUID=U.UserUID, @UserID=U.UserID
FROM PS_GameData.dbo.Chars AS C INNER JOIN PS_UserData.dbo.Users_Master AS U
ON C.UserUID = U.UserUID
WHERE C.CharName = @CharName AND C.Del=0;
IF @@ROWCOUNT=0
BEGIN
PRINT 'Character nicht gefunden.';
return;
END
ELSE
BEGIN
SET @Point2=(SELECT Point FROM PS_UserData.dbo.Users_Master WHERE UserUID=@UserUID)
UPDATE PS_UserData.dbo.Users_Master
SET Point=(Point+@Point)
WHERE UserUID=@UserUID
SET @Points=(SELECT Point FROM PS_UserData.dbo.Users_Master WHERE UserUID=@UserUID)
PRINT @CharName + 's Derzeitigen Punkte: ' + convert(varchar(30), @Point2) + '
Der Account - ' + @CharName + ' - hat seine Punkte bekommen.
Verbundener Accountname zum Charakter: "' + @UserID + '"
Aktuelle Punkte des Accounts: ' + convert(varchar(30), @Points)
END



-----Ban------

UPDATE [PS_UserData].[dbo].[Users_Master]
SET [Status] = '15'
WHERE UserUID = (SELECT UserUID
FROM [PS_GameData].[dbo].[Chars]
WHERE Del = 0 and CharName = '**********')


-----Char search-----

Use PS_GameData
Select UserId, UserUid, CharId, CharName, Del, level
from dbo.Chars where CharName like '%****%'


----Drops----

UPDATE PS_GameDefs.dbo.Mobitems SET Droprate = ***
where Grade = ***


---find free grades----

create table #a (num int)
declare @i int
set @i=0
while @i < 999
begin
set @i=@i+1
if not exists (select * from ps_gamedefs.dbo.items where grade = @i)
insert #a select @i
end
select * from #a

Credits to Zargon!

----create guild per sql------

INSERT INTO PS_GameData.dbo.Guilds


(GuildID,GuildName,MasterUserID,MasterCharID,Maste rName,Country,TotalCount,GuildPoint,CreateDate,Del eteDate)

VALUES
-- 66 ist die Guild ID die muss immer Fortlaufend bleiben
-- UserID,CharID und CharName des Gildenleaders mithilfe von charsuche für Gildenerstellung ermitteln
-- Land 1=Dark 0=Light
('1','*********','******','**','******','0',0,0,GE TDATE(),NULL)

INSERT INTO PS_GameData.dbo.GuildDetails


(GuildID,UseHouse,BuyHouse,Rank,Etin,EtinReturnCnt ,KeepEtin,Remark)

VALUES
('1',0,0,31,0,0,0,NULL)

INSERT INTO PS_GameData.dbo.GuildChars

(GuildID,CharID,GuildLevel,Del,JoinDate,LeaveDate)

VALUES
('1','**',1,0,GETDATE(),NULL)

---logs leeren-----

TRUNCATE TABLE [PS_chatlog].[dbo].[chatlog];
TRUNCATE TABLE [PS_Gamelog].[dbo].[Actionlog];

---Quest items Reccbar machen-----

USE PS_GameData

UPDATE CharItems
SET Maketype='S'
WHERE Maketype='Q'

---char ress per sql-----
USE [PS_GameData]
DECLARE
@User varchar(12),
@Char varchar(30),
@Slot tinyint,
@Country tinyint,
@Family tinyint,
@Del tinyint

SET @Char = '******'--- charname in ''

SELECT @User=um.UserID, @Country=umg.Country, @Family=c.family, @Del=c.del
FROM PS_UserData.dbo.Users_Master AS um
INNER JOIN PS_GameData.dbo.UserMaxGrow AS umg ON umg.UserUID = um.UserUID
INNER JOIN PS_GameData.dbo.Chars AS c ON c.UserUID = um.UserUID
WHERE c.CharName = @Char

IF(@@ROWCOUNT = 0)
BEGIN
PRINT 'Character "' +@Char+ '" does not exist, or does not have a related account.'
RETURN
END
ELSE IF(@@ROWCOUNT > 1)
BEGIN
PRINT 'There are multiple characters named "' +@Char+ '".'
RETURN
END
ELSE IF(@Del != 1)
BEGIN
PRINT 'Character "' +@Char+ '" is not dead.'
RETURN
END

-- Slot Selection
SELECT @Slot=ISNULL(MIN(Slots.Slot),-1) FROM
(SELECT 0 AS Slot UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS Slots
LEFT JOIN
(SELECT c.Slot
FROM PS_UserData.dbo.Users_Master AS um
INNER JOIN PS_GameData.dbo.Chars AS c ON c.UserUID = um.UserUID
WHERE um.UserID = @User
AND c.Del = 0) AS Chars ON Chars.Slot = Slots.Slot
WHERE Chars.Slot IS NULL

IF(@Slot = -1)
BEGIN
PRINT 'Account "' + @User + '" does not have any open slots.'
RETURN
END

IF(@Slot IN(0,1,2,3,4))
BEGIN
IF(@Country=0 AND @Family IN (0,1))
BEGIN
IF(@Family IN (0,1))
BEGIN
UPDATE PS_GameData.dbo.Chars
SET DEL = 0
,Map = 42
,PosX = 47
,PosY = 2
,PosZ = 52
,RemainTime = 0
,Slot = @Slot
WHERE UserID = @User
AND CharName = @Char
AND Del = 1
IF(@@ROWCOUNT = 1)
BEGIN
PRINT 'Character "' +@Char+ '" resurrected!'
RETURN
END
END
END
ELSE IF(@Country=1)
BEGIN
IF(@Family IN (2,3))
BEGIN
UPDATE PS_GameData.dbo.Chars
SET DEL = 0
,Map = 42
,PosX = 47
,PosY = 2
,PosZ = 52
,RemainTime = 0
,Slot = @Slot
WHERE UserID = @User
AND CharName = @Char
AND Del = 1
IF(@@ROWCOUNT = 1)
BEGIN
PRINT 'Character "' +@Char+ '" resurrected!'
RETURN
END
END
END
ELSE
BEGIN
PRINT 'Invalid country.'
END
END

---Tag Adding--

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

SET CharName= '*******'
WHERE CharName= '********'


---Player search----
Use PS_GameLog
Select UserID, CharName, CharLevel, MapID, ActionTime, ActionType, Text1, Text2, Text3, Text4
from dbo.ActionLog where charname like '%*****%'


---IP search-----

Use PS_GameLog
Select UserID, UserUID, CharID, CharName, Text1, Actiontime, Mapid
from dbo.ActionLog where Text1 like '%********%'


---Check dupe Items-----

----Sondos Dub´s aufdecken Script-----
----sehr einfacher code, man kann nach belieben die spalten für mehr informationen ergänzen---
----einfach nach Select die spalten einfügen die noch benötigt werden---

----Character--Items--Überprüfen-----

USE PS_GameData
SELECT ItemUID, CharID, Craftname, Count

FROM dbo.CharItems
WHERE Exists (
SELECT ItemUID FROM dbo.Charitems dub WHERE dbo.Charitems.ItemUID = dub.ItemUID and dbo.Charitems.CharID <> dub.CharID )
ORDER BY CharId;

----Warenlager--Items--Überprüfen----

USE PS_GameData
SELECT ItemUID, UserUID, Craftname, Count
FROM dbo.UserStoredItems
WHERE Exists (
SELECT ItemUID FROM dbo.UserStoredItems dub WHERE dbo.UserStoredItems.ItemUID = dub.ItemUID and dbo.UserStoredItems.UserUID <> dub.UserUID )
ORDER BY UserUID;

----Auctionshaus--überprüfen----

USE PS_GameData
SELECT ItemUID, MarketID, Craftname, Count
FROM dbo.MarketItems
WHERE Exists (
SELECT ItemUID FROM dbo.MarketItems dub WHERE dbo.MarketItems.ItemUID = dub.ItemUID and dbo.MarketItems.MarketID <> dub.MarketID )
ORDER BY MarketID;

----Gildenlager--überprüfen----

USE PS_GameData
SELECT ItemUID, GuildID, Craftname, Count
FROM dbo.GuildStoredItems
WHERE Exists (
SELECT ItemUID FROM dbo.GuildStoredItems dub WHERE dbo.GuildStoredItems.ItemUID = dub.ItemUID and dbo.GuildStoredItems.GuildID <> dub.GuildID )
ORDER BY GuildID;

ich glaube ja das sind die meisten scripts die ich so täglich benutze.