Useful SQL Queries

[FelixBeta]

Here are various SQL queries that can help some administrators/developers to work with accounts and characters. All queries are mostly for 2.2.3 and newer versions. Feel free to add your own useful queries (place them in hide for portability).

Create GM account

DECLARE @RC int
DECLARE @id varchar(13)
DECLARE @pw varchar(13)
DECLARE @depart varchar(32)
DECLARE @class varchar(12)
DECLARE @name varchar(12)
DECLARE @birthday varchar(10)
DECLARE @grade int
DECLARE @subgrade int
SELECT @id = 'your_login'
SELECT @pw = 'your_password'
SELECT @depart = 'none'
SELECT @class = 'GM'
SELECT @name = 'name'
SELECT @birthday = 'None'
SELECT @grade = 2
SELECT @subgrade = 3
EXEC @RC = [RF_User].[dbo].[pInsert_Staff] @id, @pw, @depart, @class, @name, @birthday, @grade, @subgrade
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: RF_User.dbo.pInsert_Staff'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine

Create player account

INSERT INTO RF_User.dbo.tbl_rfaccount (id,password,accounttype,birthdate) VALUES ((CONVERT(binary, 'your_login')), (CONVERT(binary, 'your_password')),'0', '1/02/1980');

Extra: Use billing transfer query to fill in billing data for this account

Create guild

USE RF_World

DECLARE @guildname varchar(17)
DECLARE @leader varchar(17)
DECLARE @race int
DECLARE @CreateDt datetime
DECLARE @MemberCount smallint
DECLARE @MasterSerial int
DECLARE @MasterBeforeGrade tinyint
SELECT @guildname = 'PlayerGuild' -- desired guild name
SELECT @leader = 'FelixBeta' -- char ingame name
SELECT @race = (SELECT CASE Race WHEN 0 THEN 0 WHEN 1 THEN 0 WHEN 2 THEN 1 WHEN 3 THEN 4 WHEN 4 THEN 2 END FROM tbl_base WHERE Name = @leader)
SELECT @CreateDt = GETDATE()
SELECT @MemberCount = 1
SELECT @MasterSerial = (select Serial from tbl_base where Name = @leader )
SELECT @MasterBeforeGrade = 0

INSERT INTO tbl_Guild (id, Race,CreateDt,MemberCount,MasterSerial,MasterBeforeGrade)
VALUES (@guildname, @Race,@CreateDt,@MemberCount,@MasterSerial,@MasterBeforeGrade)

DECLARE @GuildSerial int
SELECT @GuildSerial = (select Serial from tbl_Guild where id = @guildname )

UPDATE tbl_general
SET GuildSerial = @GuildSerial,
GuildRank = '1'
WHERE Serial = @MasterSerial

View all GM accounts

select serial,(convert(varchar, id)), (convert(varchar, PW)) from rf_user.dbo.tbl_staffaccount

View all player accounts

select (convert(varchar, id)), (convert(varchar, password)) from rf_user.dbo.tbl_rfaccount

View all player accounts information (login, password, fireguard, ip)

use rf_user
select serial as serial,
convert(varchar,tbl_rfaccount.ID) as login,
convert(varchar,tbl_rfaccount.password) as password,
convert(varchar,uilock_pw) as fireguard,
convert(varchar,uilock_hintanswer) as fireguard_hint,
createip as ip,
lastconnectip as lastip,
createtime as createdate
from tbl_rfaccount inner join tbl_UserAccount on tbl_rfaccount.id=tbl_useraccount.id

Change player account password

use RF_User
update tbl_rfaccount set Password=convert(binary,'new_password') where ID=convert(binary,'current_login')

Change player account fireguard password

UPDATE tbl_UserAccount set uilock_pw=convert(binary(13),'new_password') FROM [RF_User].[dbo].[tbl_UserAccount] WHERE id=convert(binary, 'current_login')

Change player account fireguard hint

UPDATE tbl_UserAccount set uilock_hintanswer=convert(binary(13),'new_password') FROM [RF_User].[dbo].[tbl_UserAccount] WHERE id=convert(binary, 'current_login')

Change player class

use RF_World UPDATE tbl_base set Class='class_code' WHERE Name='player_name'

Change player pvp point

UPDATE [RF_World].[dbo].[tbl_pvporderview] SET [PvpPoint]='points' WHERE [Serial]='player_serial';
Use RF_World;
UPDATE tbl_general Set PvpPoint = 0 WHERE Serial = 'player_serial'

Change player contribution point

use rf_world
update tbl_general set PvpPoint=999999 where serial = ( select serial from tbl_base where name='player_name' )
delete from tbl_npcdata where serial = ( select serial from tbl_base where name='player_name' )

Change bank dalant/disena/cp and gold

UPDATE RF_World.dbo.tbl_AccountTrunk SET Dalant0='9999', Gold0='9999' WHERE AccountSerial='123'

Information:
Dalant0/Gold0 = Bellato
Dalant1/Gold1= Cora
Dalant2/Gold2 = Accretia

Change process point, kill point, gold point

update     t1
set

/****** Process point  ******/
ActionPoint_0 = ActionPoint_0 + '0',

/****** Kill point  ******/
ActionPoint_1 = ActionPoint_1 + '0',

/****** Gold point ******/
ActionPoint_2 = ActionPoint_2 + '0'
from tbl_supplement as t1
inner join tbl_base as t2
on t1.Serial = t2.Serial
where t2.name='player_name'

Block player character

USE [RF_USER]

DECLARE @RC int
DECLARE @nSerial int
DECLARE @nPeriod int
DECLARE @nKind int
DECLARE @szReason varchar(32)

/****** Player serial  ******/
SELECT @nSerial = 12345
/****** Period in minutes  ******/
SELECT @nPeriod = 9999
/****** 0 normal ban, 1 chat ban  ******/
SELECT @nKind = 0
SELECT @szReason = 'your_ban_reason'

EXEC @RC = [RF_User].[dbo].[pInsert_UserBan] @nSerial, @nPeriod, @nKind, @szReason
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: RF_User.dbo.pInsert_UserBan'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine

Transfer player accounts to BILLING database (Roslaw)

INSERT INTO BILLING.dbo.tbl_UserStatus (id, Status, Cash, PremiDay) SELECT id, 1, 0, 0 FROM RF_USER.dbo.tbl_rfaccount

UPDATE RF_USER.dbo.tbl_rfaccount SET accounttype = 1

Change archon/council

USE RF_World
DECLARE @eSerial int
DECLARE @Race tinyint
DECLARE @Lv tinyint
DECLARE @Rank int
DECLARE @Grade tinyint
DECLARE @PvpPoint float
DECLARE @ASerial int
DECLARE @AName varchar(17)
DECLARE @GSerial int
DECLARE @GName varchar(17)
DECLARE @WinCnt int
DECLARE @ClassType tinyint
DECLARE @State tinyint
DECLARE @Score int
DECLARE @Refund tinyint
DECLARE @dtUpScore datetime
SELECT @AName = 'FelixBeta' -- char ingame name, if supporters, they should be in guild
SELECT @eSerial = (select TOP 1 Serial from tbl_patriarch_elect ORDER BY Serial DESC)
SELECT @Race = (SELECT CASE Race WHEN 0 THEN 0 WHEN 1 THEN 0 WHEN 2 THEN 1 WHEN 3 THEN 4 WHEN 4 THEN 2 END FROM tbl_base WHERE Name = @AName)
SELECT @Lv = (select Lv from tbl_base where Name = @AName)
SELECT @Rank = 0 -- pvp rank, can leave zero
SELECT @Grade = 0 -- pvp rank grade, can leave zero
SELECT @PvpPoint = 0 -- pvp point, can leave zero
SELECT @ASerial = (select Serial from tbl_base where Name = @AName)
SELECT @GSerial = (select GuildSerial from tbl_general where Serial = @ASerial)
SELECT @GName = (select id from tbl_Guild where Serial = @GSerial)
SELECT @WinCnt = 1 -- can leave zero, i guess
SELECT @ClassType = 0 --archon 0,l_consule 1,l_striker 2,l_def 3,left_sup 4,r_consule 5,r_striker 6,r_def 7,r_sup 8,failed on election=255
SELECT @State = 2 --1 failed, 2 chosen by game, 3 is chosen by archon, lets hope it will do for both types of election
SELECT @Score = 0
SELECT @Refund = 0
SELECT @dtUpScore = GETDATE()

INSERT INTO tbl_patriarch_candidate (eSerial,Race,Lv,Rank,Grade,PvpPoint,ASerial,AName,GSerial,GName,WinCnt,ClassType,State,Score,Refund,dtUpScore)
VALUES (@eSerial,@Race,@Lv,@Rank,@Grade,@PvpPoint,@ASerial,@AName,@GSerial,@GName,@WinCnt,@ClassType,@State,@Score,@Refund,@dtUpScore)

 

[Ozzy]

Legend! Thanks for these