SQL Query To Delete Empty Accounts
Most of you might recognise this: after a while your server has almost as much accounts as it has characters, meaning there are quite a few accounts that never even got used. A waste of good serverspace!
Now I got quite fed up with that, so decided to make a SQL query which would solve this once and for all, and here it is:
HOW TO USE:
Easiest way is with your SQL Query Analyser. Open it (Start -> Programs -> Microsoft SQL Server -> Query Analyzer), login and select the MuOnline database from the drop-down menu on top. Now simply copy-paste the code and press F5 to run it!
RUN THIS ONLY FIRST TIME!
Code:
alter table memb_info
add active int
alter table VI_CURR_INFO
add active int
RUN THIS EVERYTIME YOU WANT TO DELETE EMPTY ACCOUNTS
Code:
Update dbo.memb_info
SET active = 0
Update dbo.memb_info
SET active = 1 FROM memb_info join Character on memb_info.memb___id=Character.AccountID
collate Latin1_general_CI_AS
Update dbo.VI_CURR_INFO
SET active = 0
Update dbo.VI_CURR_INFO
SET active = 1 FROM VI_CURR_INFO join Character on VI_CURR_INFO.memb___id=Character.AccountID
collate Latin1_general_CI_AS
SELECT * FROM VI_CURR_INFO join MEMB_INFO on VI_CURR_INFO.memb___id=MEMB_INFO.memb___id
collate Latin1_general_CI_AS WHERE memb_info.active = 0 AND VI_CURR_INFO.active=0
DELETE FROM VI_CURR_INFO WHERE VI_CURR_INFO.active=0
DELETE FROM MEMB_INFO WHERE memb_info.active = 0
There you go, all accounts that have no characters in them are deleted and you get a nice list of all the deleted accounts! You can even set this as a recurring job, so for instance each week theres a empty-characters sweep :)
Note: this only works with accounts that have been made with the idreg page of just about every php package i know, which inserts only a new field in the MEMB_INFO and VI_CURR_INFO table.
