I want to know how to clean ID and character data in the database.

[thetoys]

I want to know how to clean ID and character data in the database.

[C_O_R_E]

I'm not familiar with SQL in general and haven't looked into flyff databases.
By a quick eye catch, just saw that CHARACTER01 stores all created ones, which might be the right database to wipe out. If you want to kick out also every created account, drop all entries in ACCOUNT database.

TRUNCATE - clears all entries/records of a specified table but causing problems with PK/FK
See:
as @ mentioned. (Haven't known that)

READ MORE about TRUNCATE


Not sure about LOGGING_01 database.


$Edit: Ok, as it seems like all tables (regarding their entries) of LOGGING also needs to get kicked out.

$Edit2: Do not truncate BASE_VALUE_TBL since it contains all base values for new characters based on gender.

[xTwiLightx]

Better use delete + checkident to really "reset" the databases - truncate only deletes all entries while having problems with foreign keys/constaints.

USE ACCOUNT_DBF
GO

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DBCC CHECKIDENT (''?'', RESEED, 1)'
GO

USE CHARACTER_01_DBF
GO

EXEC sp_MSForEachTable @ command1 = 'ALTER TABLE ? NOCHECK CONSTRAINT ALL',  @ whereand ='and o.name <> ''BASE_VALUE_TBL'''
EXEC sp_MSForEachTable @ command1 = 'DELETE FROM ?',  @ whereand ='and o.name <> ''BASE_VALUE_TBL'''
EXEC sp_MSForEachTable @ command1 = 'ALTER TABLE ? CHECK CONSTRAINT ALL',  @ whereand ='and o.name <> ''BASE_VALUE_TBL'''
EXEC sp_MSForEachTable @ command1 = 'DBCC CHECKIDENT (''?'', RESEED, 1)',  @ whereand ='and o.name <> ''BASE_VALUE_TBL'''
GO

(remove the spaces between the '@' and the variable names "command1" and "whereand")

Better back up your BASE_VALUE_TBL, even if this script should exclude it.

USE LOGGING_01_DBF
GO

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DBCC CHECKIDENT (''?'', RESEED, 1)'
GO

Regarding the RANKING_DBF: I bet no one really uses it, so it can be ignored.

[KingKeesie]

Yes but be sure you only delete entries with idplayer because there are skill tables and tables like BASE and stuff

[xTwiLightx]

Originally Posted by KingKeesie

Yes but be sure you only delete entries with idplayer because there are skill tables and tables like BASE and stuff

For character-based data, only the BASE_VALUE_TBL data is important to be kept.

Logging data can be cleared for sure (because old logs won't fit the new character data anymore), account data can be kept but isn't a must.