[Release] Many usefull scripts For Your Mu Online Server

[Angerfist]

Here is some verry helpfull scripts for Mu Online servers.


----------------------
Delete all Guilds Member
Code:

delete from guildmember

----------------------
Delete all Guilds
Code:

delete from guild

----------------------
Delete all Warehouses
Code:

delete from warehouse

----------------------
Delete all Characters
Code:

delete from Character

----------------------
Delete all resets
Code:

delete from memb_info

----------------------
Drop Webshop tables

Code:

drop table WebShop
Go
drop table WebShop_LOG
Go
drop table WebShop_UI
Go
drop table MEMB_CREDITS
go
drop procedure XCredits
go
Drop table WebShop_Groups
Go
Drop table WebShop_Feedback
Go
drop table WebShop_IpBlock
Go

----------------------
Delete no member Guild ( guild have 1 GM only ) :

alter table guild nocheck constraint all
alter table guildmember nocheck constraint all
delete from guild
where EXISTS (
SELECT m.G_Name,count(*) as memb from GuildMember m
where guild.g_name=m.G_name
group by m.G_Name
having count(*) <2
)

----------------------
Delete guildmember with no guild ( when you edited wrong then make bug )

alter table guild nocheck constraint all
alter table guildmember nocheck constraint all
delete from guildmember
where NOT EXISTS (
SELECT * from Guild
where guild.g_name=guildmember.G_name
)

----------------------
Top 10 Guild ( as members )

SELECT top 10 g.g_name,count(all m.g_name)as thanhvien from Guild as g join Guildmember as m
on g.g_name like m.g_name
group by g.g_Name
having count(*) > 20
order by count(*) DESC

----------------------
Top 5 guild ( as resets all members in guild )

select top 5 g.g_name,sum(c.resets) as reset from guildmember g join character c
on g.name like c.name
group by g.g_name
order by reset desc

----------------------
Top 5 online time

select top 10 c.name,resets,s.onlinehours from character c full join memb_stat s
on c.accountid=s.memb___id
where (c.accountid in ( select top 10 memb___id from memb_stat
order by onlinehours desc ))
and (c.name in ( select top 1 c.name from character c where c.accountid=s.memb___id order by resets desc))
order by onlinehours desc

----------------------
Top average resets (per player in guild ).Edit 10 to value you want . That's value of min member of a guild.

select top 5 g.g_name,sum(c.resets)/count(all g.g_name) as reset from guildmember g join character c
on g.name like c.name
group by g.g_name
having count(all g.g_name)>10
order by reset desc

----------------------
rename account

select * from accountcharacter
where id='9242085'

update accountcharacter
set id='ha1710'
where id='9242085'

select * from accountcharacter
where id='ha1710'
-----------------------
select * from character
where accountid='9242085'

update character
set accountid='ha1710'
where accountid='9242085'

select * from character
where accountid='ha1710'
----------------------
select * from memb_info
where memb___id='9242085'

update memb_info
set memb___id='ha1710'
where memb___id='9242085'

select * from memb_info
where memb___id='ha1710'
----------------------
select * from memb_stat
where memb___id='9242085'

update memb_stat
set memb___id='ha1710'
where memb___id='9242085'

select * from memb_stat
where memb___id='ha1710'
-----------------------
select * from warehouse
where accountid='9242085'

update warehouse
set accountid='ha1710'
where accountid='9242085'

select * from warehouse
where accountid='ha1710'

----------------------
delete account that no connect ( blank account ) or longtime no connect

delete from memb_info
where memb___id not in (
select memb___id from memb_stat)

delete from accountcharacter
where id not in (
select memb___id from memb_stat)

delete from character
where accountid not in (
select memb___id from memb_stat)

delete from warehouse
where accountid not in (
select memb___id from memb_stat)

----------------------

Character not in account :

select accountid,character.name from character where accountid in (
select accountid from character
where name not in (select gameid1 from accountcharacter where id=accountid )
and name not in (select gameid2 from accountcharacter where id=accountid )
and name not in (select gameid3 from accountcharacter where id=accountid )
and name not in (select gameid4 from accountcharacter where id=accountid )
and name not in (select gameid5 from accountcharacter where id=accountid )
)

----------------------
delete guild , guildmember that wrong :

alter table guild nocheck constraint all
alter table guildmember nocheck constraint all
delete from guild
where g_master not in ( select name from character )
delete from guildmember
where name not in ( select name from character )
delete from guildmember
where G_Name not in ( select G_name from guild )

----------------------
back up server :

--trunkating
BACKUP LOG muonline WITH NO_LOG

USE [Muonline]
GO
--shrinking
DBCC SHRINKDATABASE(N'Muonline')

Declare @dateBackup Varchar(100)
Set @dateBackup = 'c:\muonline_' +
Convert(varchar, datepart( year , Getdate() )) + '-'+
Convert( varchar , datepart( month , Getdate() ) ) + '-'+
Convert( varchar, datepart( day , Getdate() ) ) + '.bak'
--saving backup with name and date.
BACKUP DATABASE [Muonline] TO DISK = @dateBackup WITH NOFORMAT, NOINIT, NAME = N'Muonline-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

----------------------

Negative Money Fix :

Code:

UPDATE Character
SET Money='2000000000'
WHERE Money<-1

--------------------------------

Personal ID Code Check "Disabling" :

Code:

UPDATE MEMB_INFO
SET sno__numb=('')
WHERE sno__numb>1

-----------------------------

Starting Money (You starting money by changing ('10000000') to some other value.) :

Code:

UPDATE Character
SET Money=('10000000')
WHERE Money<1

-------------------------------

Auto Stats Fix :



Code:

UPDATE Character
SET Strength='32766'
WHERE Strength<1

UPDATE Character
SET Dexterity='32766'
WHERE Dexterity<1

UPDATE Character
SET Vitality='32766'
WHERE Vitality<1

UPDATE Character
SET Energy='32766'
WHERE Energy<1

If there is a Dark Lord Then it's will look so :



Code:

UPDATE Character
SET Strength='32766'
WHERE Strength<1

UPDATE Character
SET Dexterity='32766'
WHERE Dexterity<1

UPDATE Character
SET Vitality='32766'
WHERE Vitality<1

UPDATE Character
SET Energy='32766'
WHERE Energy<1

UPDATE Character
SET Leadership='32766'
WHERE Leadership<1

---------------------------------

Auto Clear PK


Code:

UPDATE Character
SET PkLevel = ('0'), PkTime = ('0'), PkCount = ('0')
Where PkLevel>2

--------------------------------------


Starting stats edditing:


Code:

UPDATE Character
SET Strength='how mutch starting strenght you wanth'
WHERE Strenght<18

UPDATE Character
SET Dexterity='how mutch starting dexterity you wanth'
WHERE Dexterity<16

UPDATE Character
SET Vitality='how much starting vitality you wanth'
WHERE Vitality<16

UPDATE Character
SET Energy='how much starting energy you wanth'
WHERE Energy<16

For versions that have Dark Lord :



Code:

UPDATE Character
SET Strength='how mutch starting strenght you wanth'
WHERE Strenght<18

UPDATE Character
SET Dexterity='how mutch starting dexterity you wanth'
WHERE Dexterity<16

UPDATE Character
SET Vitality='how much starting vitality you wanth'
WHERE Vitality<16

UPDATE Character
SET Energy='how much starting energy you wanth'
WHERE Energy<16

UPDATE Character
SET Leadership='how much starting command you wanth'
WHERE Leadership<15

-----------------------

Incorect connected users shown on the web fix. (First Take Server Offline !) (If your server is offline maybe there is connected users shown on the web, it will fix that).



Code:

UPDATE MEMB_STAT
SET Connect_Stat='0'
WHERE Connect_Stat>0

-------------------------

Thise script will automaticaly teleports users from "The Place Of Excile" To Lorencia, bar !

Code:

UPDATE Character
SET MapNumber=('0'), MapPosX=('125'), MapPosY=('125')
WHERE MapNumber=('5')

---------------------

Thise one will fix guild score -99 bug.

Code:

UPDATE Guild
SET G_Score=('0')
WHERE G_Score<-1

---------------------

Thise delete all there resets (DONT USE IT LIKE JOB).

Code:

UPDATE Character
SET Resets=('0')
WHERE Resets>0

----------------------

Thise one will clear all the vaults.

Code:

UPDATE warehouse
SET Items=('')

----------------------

Thise one for deleting all the items from inventories.

Code:

UPDATE Character
SET Inventory=('')

----------------------

Thise one will unban all the players.

Code:

UPDATE Character
SET CtlCode=('0')
WHERE CtlCode=('1')

-----------------------

Thise one will delete all GMs.

Code:

UPDATE Character
SET CtlCode=('0')
WHERE CtlCode='32' OR CtlCode='8'

------------------------

Thise one will delete all the level up points.

Code:

UPDATE Character
SET LevelUpPoints=('0')
WHERE LevelUpPoints>0

------------------------

Auto Reset

Code:

UPDATE Character
SET cLevel=('1') , experience=('0')
WHERE cLevel>349

---------------------

Thise delete all there resets (DONT USE IT LIKE JOB).

Code:

UPDATE Character
SET Resets=('0')
WHERE Resets>0

------------------------

Thise one will delete all the level up points.

Code:

UPDATE Character
SET LevelUpPoints=('0')
WHERE LevelUpPoints>0

------------------------
Give a NO of starting Level up points to characters.
Edit: 1500 with how much or less do you want.

Code:

UPDATE Character
SET LevelUpPoint=('') + 1500
WHERE cLevel<=1 and Resets<=1

-------------------------

This one will add free credits for ALL players


Code:

Update MEMB_CREDITS
SET credits=credits+100

---------------------------------

This one will drop MuOnline database (better dont use it ^^)

Code:

DROP dbo.MuOnline

---------------------------------

This one will show you top 100 credited accounts

Code:

SELECT TOP 100 FROM MEMB_CREDITS
WHERE credits>0

---------------------------------

This one will delete all level up points for characters who has max stats

Code:

update Character set leveluppoint='0'
where
Strength+Dexterity+Vitality+Energy='131068'
OR
Strength+Dexterity+Vitality+Energy+Leadership='163835'

Когато искате да дадете на всички Account-и кредити , пишете това в Query Analyzer-a.
Пример : Ако са имали 100 кредита , след като напишете кода ще даде на всички кредити и ще им станат 5100

Code:

UPDATE MEMB_INFO SET credits = credits + 5000

Когато искате да дадете кредити само на един Account , пишете това в Query Analyzer-a.
Пример : Ако сте имали 100 кредита , след като напишете кода ще даде само на един акаунт кредити и ще станат 5100

Code:

UPDATE MEMB_INFO SET credits = credits + 5000 WHERE memb___id = 'AccountID'

Когато искате да заместите кредитите на всички , пишете това в Query Analyzer-a.
Пример : Ако са имали 100 кредита , след като напишете кода ще им замести кредитите и ще им станат 5000

Code:

UPDATE MEMB_INFO SET credits = 5000

Когато искате да заместите кредити само на един Account , пишете това в Query Analyzer-a.
Пример : Ако сте имали 100 кредита , след като напишете кода ще ви замести кредитите и ще ви станат 5000

Code:

UPDATE MEMB_INFO SET credits = 5000 WHERE memb___id = 'AccountID'

Намаля логовете,съответно се намаля и лага особено ако сте със по слаба машина.

BACKUP LOG MuOnline WITH TRUNCATE_ONLY DBCC SHRINKFILE (MuOnline_log, 100)

BACKUP DATABASE

USE master;
GO
BACKUP DATABASE MuOnline
TO DISK = 'D:\MuServer\backups\MuOnline-backup.Bak'
WITH FORMAT,
MEDIANAME = 'DarksTeam',
NAME = 'Full Backup of MuOnline MSSQL script www.darksteam.net';
GO

Only this thread will be updated now !

Please post all mistekes in this thread.

Updated (22.03.2014)

 

[128ve980]

Защо правиш темите си на английски след като и бъкел не знаеш? Само в тази ти тема има повече грешки от колко верни думи имаш..

 

[HOLIGAN]

Защо правиш темите си на английски след като и бъкел не знаеш? Само в тази ти тема има повече грешки от колко верни думи имаш..

Мисли малко и ти coppy/paste директно не си играе с превод (facepalm)

 

[Wikko0]

Темата по-скоро е за Guide не за Release

 

[jannijanni]

Английският език ти е на ниво като гледам

 

[RaFa]

UPDATE Character
SET Strength='32766'
WHERE Strength<1
 
UPDATE Character
SET Dexterity='32766'
WHERE Dexterity<1
 
UPDATE Character
SET Vitality='32766'
WHERE Vitality<1
 
UPDATE Character
SET Energy='32766'
WHERE Energy<1
 
[U][B]UPDATE Character
SET Leadership='32766'
WHERE Leadership<1[/B][/U]

И така ще сложи и на другите класове по 32766 команда

 

[VISITOR]

10/10 nice thx

 

[hrisiz]

UPDATE Character
SET Strength='32766'
WHERE Strength<1
 
UPDATE Character
SET Dexterity='32766'
WHERE Dexterity<1
 
UPDATE Character
SET Vitality='32766'
WHERE Vitality<1
 
UPDATE Character
SET Energy='32766'
WHERE Energy<1
 
[U][B]UPDATE Character
SET Leadership='32766'
WHERE Leadership<1[/B][/U]

И така ще сложи и на другите класове по 32766 команда

true

ето как би трябвало да стане мисля
UPDATE Character
SET Leadership='32766'
WHERE Leadership<1 AND Class = 64

Всъщност навсякъде където става дума за Update на DL добавете " AND Class = 64"

 

[RaFa]

При по-новите версии и 65/66
или направо

UPDATE Character SET Leadership='32766' WHERE Leadership<1 and Class > 60 and Class < 70

 

[Raganius]

"Verry many"

 

[sigheyes]

How about a newly created account which automatically auto add credits?
like starting credits on new account?

 

[MaPaXyuA]

How about a newly created account which automatically auto add credits?
like starting credits on new account?

 

[pafa7a]

How about a newly created account which automatically auto add credits?
like starting credits on new account?

For this you must edit your register function to add information in MEMB_CREDITS table
If you wanna add credits to all accounts (not future registered) use this :
UPDATE MEMB_CREDITS SET Credits=Credits+X
where X is the number of credits

 

[DemonuMu]

for 97d.99i no works

 

[Angerfist]

update.

 

[Raganius]

How about a newly created account which automatically auto add credits?
like starting credits on new account?

I will make 50 accounts and buy all items. trade from other pc. Your server gonna be FAIL.

 

[alf1234]

but all thise is writen by my hands, so it can contain mistakes. Please post all my mistekes
ahahahahahaaaa

 

[Angerfist]

Update.

 

[NewEmpire]

Who can help with respawn script after switch or select server to apear in the city?