SQL Query To Delete Empty Accounts

[FragFrog]

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!

alter table memb_info 
add active int
alter table VI_CURR_INFO
add active int

RUN THIS EVERYTIME YOU WANT TO DELETE EMPTY ACCOUNTS

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.

 

[themad]

fragfrog have u tested this query ? =)

 

[themad]

never mind .. i just did =) nice work

 

[Backe]

Nice work dude , I just tryed it and it works fine

 

[FragFrog]

fragfrog have u tested this query ? =)

Ofcourse

Works fine here, just NEVER forget the 'where' part when running it or your server might suddenly feel a bit quiet if you get what I mean

 

[tyrenn]

why delete empty characters? if people will use them later.
just delete inactive accounts ^^

 

[FragFrog]

why delete empty characters? if people will use them later.
just delete inactive accounts ^^

If people will use them later they should make them later. I deleted about 150 accounts in the past week alone. Eventually a big server will have hundrest of unused accounts, meaning other people may not be able to use the name they like because some stick already registred it, and also making the database slower and slower because of unneeded rows.

And how would you devine an inactive account? Maybe someone is just on holiday for a few weeks, or in hospital. Not really nice to delete their accounts they've worked on for months :icon6:

Besides, you don't HAVE to use this, you CAN. So why exactley did you even post? Besides spamming the forum with useless comments? You know I haven't seen a single GOOD piece of advice from you in any topic I see you post in; don't criticise the people who release stuff, even if its stuff you don't need yourself.

 

[lesansan]

very good scripts FragFrog.. thank you for saving my hdd spaces.

 

[fiorelloo]

finaly some1 did it

 

[Fallen Angel]

Error

I Got A Error Saying
Server: Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'active' in table 'memb_info' is specified more than once.

 

[_master]

Column exist...

I Got A Error Saying
Server: Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'active' in table 'memb_info' is specified more than once.

...open you 'memb_info' table, you must have a 'active' column, if is exist, i sugest to delete im and run script again.

 

[[GR]SiLvER]

WoW i was dreaming of it :angel_inn
now i wait for the - delete inactive accounts -

 

[Fallen Angel]

well the inactive acc already exist rite? in guide

 

[FragFrog]

I Got A Error Saying
Server: Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'active' in table 'memb_info' is specified more than once.

You sure you ran the first part of the script only once?

Since if you try to add another column named 'active' this will indeed cause that error.

 

[elche27]

Yo bro, FragFrog, nice work and nice share.....my regards!!!

 

[metallica]

i need any like butt but..need delete X pjs of X level whit restriction..example

delete (all char(dk) and lvl =1) in acc whit char lvl max = 400

u understern?...tks..

 

[themad]

i need any like butt but..need delete X pjs of X level whit restriction..example

delete (all char(dk) and lvl =1) in acc whit char lvl max = 400

u understern?...tks..

lol im not sure what u just said but...i gues u want this

delete from character where class=('16') and clevel=('1')
delete from character where class=('0') and clevel=('1')
delete from character where class=('32') and clevel=('1')

this will delete all characters who have not completed the quest and are level 1

 

[metallica]

lol im not sure what u just said but...i gues u want this

delete from character where class=('16') and clevel=('1')
delete from character where class=('0') and clevel=('1')
delete from character where class=('32') and clevel=('1')

this will delete all characters who have not completed the quest and are level 1

very tks..but need :


delete from character where [class=('16') and clevel=('1')] AND [the char lvl max of ACC <= 100]


I wan DELETE ALL CHAR (class=('16') and clevel=('1')) IN ACC WHIT CHARs <= 100

u understen my script?...

 

[HackingSkills]

try this one too. this deletes empty accounts

truncate table nullaccounts;
insert into nullaccounts SELECT a.memb___id, b.AccountID FROM MEMB_INFO a
LEFT JOIN (SELECT DISTINCT AccountID FROM [Character]) b
ON a.memb___id = b.AccountID
WHERE b.AccountID IS NULL;

declare @neym nvarchar(10)

DECLARE my_pointer CURSOR FOR Select username from nullaccounts
open my_pointer
FETCH NEXT FROM my_pointer INTO @neym WHILE
@@FETCH_STATUS=0

BEGIN
delete from memb_info where @neym=memb___id
delete from vi_curr_info where @neym=memb___id
FETCH NEXT FROM my_pointer INTO @neym
END

close my_pointer
deallocate my_pointer

you need to create a table named "nullaccounts" with fields username and status. both of nvarchar with 10 width

 

[Josh]

ROFL, just deleted 96 acc! And reg was closed! Thanks!!