SP to delete accounts with characters

Summary
It registers a new stored procedure on your MSSQL server that enabled you to delete an account, including all of it's characters and all of their data (items, skills, guild membership, you get it).
It checks whether one of the characters is a guild leader, if so, it won't touch the particular character, but continues to delete all other characters and leave the account intact.


Installation
It's quite simple: Run the query on your database server. It'll register a new stored procedure in the account db.


Usage
Execute the stored procedure with the UserNum of the account you want deleted:

Code:

---

EXEC [account].[dbo].[cabal_sp_DelAccount] UserNum

---

For example, say you want to delete account number 69:

Code:

---

EXEC [account].[dbo].[cabal_sp_DelAccount] 69

---

Where to obtain it?
Pastebin:
[T-SQL] Delete account w/ chars - Pastebin.com

Or copy it from here:

Spoiler:

Code:

---

USE [account]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE cabal_sp_DelAccount
        -- Add the parameters for the stored procedure here
        @UserNum int       
       
AS
BEGIN
        -- =============================================
        -- Author:                Alphakilo23 for TheDivinityProject.com
        -- Create date: 08.16.2011
        -- Description:        Deletes accounts, including characters and data associated with characters.
        -- Returncodes:        -1        Account doesn't exist;
        --                                -2        Account has a guildleading character.
        -- =============================================

        -- Check wether the UserNum stated in the arguments exists
        IF NOT EXISTS (SELECT [UserNum] FROM [account].[dbo].[cabal_auth_table] WHERE [UserNum] = @UserNum)
                -- It doesn't. Don't let the luser troll us, abort here.
                RETURN -1
       
        DECLARE @i int, @max int, @del int, @rowcount int, @gl bit;
                     
        SET @i  = @UserNum * 8;
        SET @max = @i + 5
        SET @gl = 0;
       
        BEGIN TRAN
                        --Iterate through all possible charactersslots on the specified account
                        WHILE @i < @max
                        BEGIN
                                        --Check if the character exists
                                        IF EXISTS (SELECT [CharacterIdx] FROM [gamedb].[dbo].[cabal_character_table] WHERE [CharacterIdx] = @i)
                                        BEGIN
                                               
                                                -- Check if the current character is a guildleader.
                                                DECLARE @c int;
                                                SELECT @c = COUNT (A.GroupIndex)
                                                                                FROM [gamedb].[dbo].[GuildGroup] A
                                                                                INNER JOIN (SELECT GroupIndex FROM [gamedb].[dbo].[GuildMember]
                                                                                        WHERE CharacterIndex = @i) B
                                                                                                ON A.GroupIndex = B.GroupIndex
                                                                                                WHERE A.GroupPermission = 0                                                       

                                                --If the character is a guildleader, don't touch him.
                                                IF ( @c != 0)
                                                BEGIN
                                                        SET @gl = 1
                                                END
                                                ELSE
                                                BEGIN                                           
                                                    -- Character exists and is no guildleader, CHARGE THE LAZER!
                                                    -- Increase killcounter ...
                                                        SET @del = @del + 1
                                                       
                                                        -- and nuke it from orbit.
                                                        DELETE FROM [gamedb].[dbo].[cabal_equipment_table]        WHERE [CharacterIdx] = @i
                                                        DELETE FROM [gamedb].[dbo].[cabal_Inventory_table]        WHERE [CharacterIdx] = @i
                                                        DELETE FROM [gamedb].[dbo].[cabal_skilllist_table]        WHERE [CharacterIdx] = @i
                                                        DELETE FROM [gamedb].[dbo].[cabal_quickslot_table]        WHERE [CharacterIdx] = @i
                                                        DELETE FROM [gamedb].[dbo].[cabal_qddata_table]                WHERE [CharacterIdx] = @i
                                                        DELETE FROM [gamedb].[dbo].[cabal_questdata_table]        WHERE [CharacterIdx] = @i
                                                        DELETE FROM [gamedb].[dbo].[cabal_record_combo]                WHERE [charIdx] = @i             
                                                        DELETE FROM [gamedb].[dbo].[cabal_character_table]        WHERE [CharacterIdx] = @i             
                                                        DELETE FROM [gamedb].[dbo].[GuildMember]                        WHERE [CharacterIndex] = @i                                                       
                                                        DELETE FROM [gamedb].[dbo].[cabal_bbead_table] where [characteridx] = @i
                                                        DELETE FROM [gamedb].[dbo].[cabal_craft_table] where [CharacterIdx] = @i
                                   
                                                        -- Also delete the buddylist and every buddylist entry associated with the char.
                                                        DELETE FROM [gamedb].[dbo].[chat_buddy_table]                WHERE [RegisteeCharIdx] = @i OR [RegisterCharIdx] = @i
                                                        DELETE FROM [gamedb].[dbo].[chat_buddygroup_table]                WHERE [CharIdx] = @i
                                                END
                        END
                       
                        -- Increase the incrementor o.O       
                        SET @i = @i + 1
                        END
                       
                        -- And again, check if the account has any guildleading chars in it.
                        IF (@gl != 1)
                        BEGIN
                        -- It hasn't, so finalize the deletion.                       
                                DELETE FROM [gamedb].[dbo].[cabal_warehouse_table]        WHERE [UserNum] = @UserNum
                                DELETE FROM [gamedb].[dbo].[ChatBuddy]                                WHERE [UserNum] = @UserNum
                                DELETE FROM [account].[dbo].[cabal_auth_table]                WHERE [UserNum] = @UserNum
                        END
                        ELSE
                        BEGIN
                                -- There is, leave the account alone ;_;
                                COMMIT TRAN
                                RETURN -2
                        END
        COMMIT TRAN
       
        -- Return the killcount.
        RETURN @del
END
GO

---

Great release, but instead of making

[gamedb].[dbo].[cabal_character_table]

isnt possible to do:

USE GAMEDB in the very first line of the fle and then just SELECT * FROM cabal_character_table?

It wont do any difference, just a cleaner code... anyway, its a hint =p

Quote:

---

Originally Posted by SheenBR

Great release, but instead of making

[gamedb].[dbo].[cabal_character_table]

isnt possible to do:

USE GAMEDB in the very first line of the fle and then just SELECT * FROM cabal_character_table?

It wont do any difference, just a cleaner code... anyway, its a hint =p

---

But that way it won't lock the db scheme, which means more performance at high throughput.

thank you for sharing Alphakilo, how about sp for deleting orphan/abondoned accounts? say without characters and/or too old like a year or specified date range.

Quote:

---

Originally Posted by Alphakilo23

But that way it won't lock the db scheme, which means more performance at high throughput.

---

Well, I dunno alot about creating stored procedures, but when I need to do a simple SELECT, UPDATE query, typing SELECT database.dbo.table annoys me then I just type USE database.

and I didnt understand what you said, using "USE database" is not possible in stored procedures or is it unsecure?

Quote:

---

Originally Posted by ARPANET

thank you for sharing Alphakilo, how about sp for deleting orphan/abondoned accounts? say without characters and/or too old like a year or specified date range.

---

I created something similar script a while ago, you could combine both scripts:
[T-SQL] Account Cleanup (w/ remarks) - Pastebin.com
If you have any trouble with it, feel free to ask me.

Quote:

---

Originally Posted by SheenBR

Well, I dunno alot about creating stored procedures, but when I need to do a simple SELECT, UPDATE query, typing SELECT database.dbo.table annoys me then I just type USE database.

and I didnt understand what you said, using "USE database" is not possible in stored procedures or is it unsecure?

---

It's okay to just rely on the USE statement that you're only running once or twice, but when you're scripting you should use .dbo. because it won't lock the DB scheme (google "MSSL db scheme lock" if you want to know more details).
It is possible to rely on USE statement in SPs but it's bad practice.

if you want I could make a program to execute this query, like cabaltoolz =p

Knock yourself out. It's "public domain", which roughly translates to "I don't give a fuck about what happens to the source code." :tongue:

sheen genial ideea you can make a program to execute query ....

is mssql 2005 manager i get this when trying to delete an account.

Code:

---

EXEC [account].[dbo].[cabal_sp_DelAccount] tester

---

Msg 8114, Level 16, State 5, Procedure cabal_sp_DelAccount, Line 0
Error converting data type nvarchar to int.

EDIT: i foud put the proble was i inserted the user name instead of the number.

for those who will encounter the same thing the usernum of an account can be found in the ACCOUNT database in the tabe dbo.cabal_auth_table

Nice one,can you give us a script to delete only the skills from the characters?Without the special Skills?

Quote:

---

Originally Posted by S37uP!Update

Without the special Skills?

---

That's fairly easy, just exclude the Skills you don't want deleted.

Can you give me the script please?Because is kind of hard for me,idk how to edit this querrys :)

Msg 208, Level 16, State 1, Procedure cabal_sp_DelAccount, Line 32
Invalid object name 'gamedb.dbo.cabal_character_table'.
Msg 266, Level 16, State 2, Procedure cabal_sp_DelAccount, Line 32
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.


error when i exec to delete

Check your database name, you might be using a different name (gamedb)... in my side, I am using "Server01". To TS, thanks for sharing.

is this working to any episode of cabal? i do this but always complete with errors.

it may, there are db changes from ep2 to ep8, just analyze the script and update it