• Новые темы в этом разделе публикуются автоматически при добавлении файла в менеджер ресурсов.
    Ручное создание новых тем невозможно.

kick

Предвестник
Administrator
За веру и верность форуму
Отец-основатель
Сообщения
7 027
Розыгрыши
21
Решения
1
Репутация
5 860
Реакции
6 523
Баллы
2 688
Делаем аккаунт премиум:
update user_account set pay_stat = 0 where account = 'ИМЯ АККАУНТА'

Изменение ника:
update user_data set char_name = 'Новый ник' where char_name= 'старый ник'

Добавление какого-то итема всем игрокам:
DECLARE @item_type INT;DECLARE @amount int;
SET @item_type = '57';
SET @amount = '1000000';


INSERT INTO user_item (char_id, item_type, amount, enchant, eroded, bless, ident, wished, warehouse)
SELECT char_id, @item_type, @amount, 0, 0, 0, 0, 0, 0
FROM user_data

Чары будут создаваться 76 лвл:
use lin2world;

ALTER PROCEDURE dbo.lin_CreateChar
(
@char_name NVARCHAR(24),
@account_name NVARCHAR(24),
@account_id INT,
@pledge_id INT,
@builder TINYINT,
@gender TINYINT,
@race TINYINT,
@class TINYINT,
@world SMALLINT,
@xloc INT,
@yloc INT,
@zloc INT,
@HP FLOAT,
@MP FLOAT,
@SP INT,
@Exp INT,
@Lev TINYINT,
@align SMALLINT,
@PK INT,
@Duel INT,
@PKPardon INT,
@FaceIndex INT = 0,
@HairShapeIndex INT = 0,
@HairColorIndex INT = 0
)
AS
SET NOCOUNT ON
SET @char_name = RTRIM(@char_name)
DECLARE @char_id int
SET @char_id = 0


IF @char_name LIKE N' '
BEGIN
RAISERROR ('Character name has space : name = [%s]', 16, 1, @char_name)
RETURN -1
END
-- check user_prohibit
if exists(select char_name from user_prohibit (nolock) where char_name = @char_name)
begin
RAISERROR ('Character name is prohibited: name = [%s]', 16, 1, @char_name)
RETURN -1
end
declare @user_prohibit_word nvarchar(20)
select top 1 @user_prohibit_word = words from user_prohibit_word (nolock) where @char_name like '%' + words + '%'
if @user_prohibit_word is not null
begin
RAISERROR ('Character name has prohibited word: name = [%s], word[%s]', 16, 1, @char_name, @user_prohibit_word)
RETURN -1
end
-- check reserved name
declare @reserved_name nvarchar(50)
declare @reserved_account_id int
select top 1 @reserved_name = char_name, @reserved_account_id = account_id from user_name_reserved (nolock) where used = 0 and char_name = @char_name
if not @reserved_name is null
begin
if not @reserved_account_id = @account_id
begin
RAISERROR ('Character name is reserved by other player: name = [%s]', 16, 1, @char_name)
RETURN -1
end
end
IF @race>4
BEGIN
RAISERROR ('Race overflow : = [%s]', 16, 1, @char_name)
RETURN -1
END


IF @race=0 and @class!=0 and @class!=10
BEGIN
RAISERROR ('Class Overflow for Human: = [%s]', 16, 1, @class)
RETURN -1
END


IF @race=1 and @class!=18 and @class!=25
BEGIN
RAISERROR ('Class Overflow for Elf: = [%s]', 16, 1, @class)
RETURN -1
END


IF @race=2 and @class!=31 and @class!=38
BEGIN
RAISERROR ('Class Overflow for DE: = [%s]', 16, 1, @class)
RETURN -1
END


IF @race=3 and @class!=44 and @class!=49
BEGIN
RAISERROR ('Class Overflow for Orc: = [%s]', 16, 1, @class)
RETURN -1
END


IF @race=4 and @class!=53
BEGIN
RAISERROR ('Class Overflow for Dwarf: = [%s]', 16, 1, @class)
RETURN -1
END


-- insert user_data
INSERT INTO user_data
( char_name, account_name, account_id, pledge_id, builder, gender, race, class, subjob0_class,
world, xloc, yloc, zloc, HP, MP, max_hp, max_mp, SP, Exp, Lev, align, PK, PKpardon, duel, create_date, face_index, hair_shape_index, hair_color_index )
VALUES
(@char_name, @account_name, @account_id, @pledge_id, @builder, @gender, @race, @class, @class,
@world, @xloc, @yloc, @zloc, @HP, @MP, @HP, @MP, 90950592, 931850677, 76, @align, @PK, @Duel, @PKPardon, GETDATE(), @FaceIndex, @HairShapeIndex, @HairColorIndex)
IF (@@error = 0)
BEGIN
SET @char_id = @@IDENTITY
INSERT INTO quest (char_id) VALUES (@char_id)
END
SELECT @char_id
if @char_id > 0
begin
-- make user_history
exec lin_InsertUserHistory @char_name, @char_id, 1, @account_name, NULL
if not @reserved_name is null
update user_name_reserved set used = 1 where char_name = @reserved_name
end
GO
Удаление всех чаров:
DELETE FROM user_surrender WHERE char_id in(select char_id from user_data)DELETE FROM user_subjob WHERE char_id in(select char_id from user_data)
DELETE FROM user_sociality WHERE char_id in(select char_id from user_data)
DELETE FROM user_skill_old WHERE char_id in(select char_id from user_data)
DELETE FROM user_skill WHERE char_id in(select char_id from user_data)
DELETE FROM user_recipe WHERE char_id in(select char_id from user_data)
DELETE FROM user_punish WHERE char_id in(select char_id from user_data)
DELETE FROM user_nobless WHERE char_id in(select char_id from user_data)
DELETE FROM user_newbie WHERE char_id in(select char_id from user_data)
delete from user_macroinfo
where user_macroinfo.macro_id in (select distinct user_macro.macro_id
from user_macro join user_macroinfo on (user_macro.macro_id = user_macroinfo.macro_id)
where user_macro.char_id in(select char_id from user_data))
DELETE FROM user_macro WHERE char_id in(select char_id from user_data)
DELETE FROM user_log WHERE char_id in(select char_id from user_data)
DELETE FROM user_journal WHERE char_id in(select char_id from user_data)
DELETE FROM user_item WHERE char_id in(select char_id from user_data) AND warehouse BETWEEN 0 AND 1
DELETE FROM user_history WHERE char_id in(select char_id from user_data)
DELETE FROM user_henna WHERE char_id in(select char_id from user_data)
DELETE FROM user_friend WHERE char_id in(select char_id from user_data)
DELETE FROM user_deleted WHERE char_id in(select char_id from user_data)
DELETE FROM user_data_moved WHERE char_id in(select char_id from user_data)
DELETE FROM user_comment WHERE char_id in(select char_id from user_data)
DELETE FROM user_ActiveSkill WHERE char_id in(select char_id from user_data)
DELETE FROM ssq_user_data WHERE char_id in(select char_id from user_data)
DELETE FROM quest WHERE char_id in(select char_id from user_data)
DELETE FROM olympiad_result WHERE char_id in(select char_id from user_data)
DELETE FROM olympiad_match WHERE char_id in(select char_id from user_data)
DELETE FROM nobless_achievements WHERE char_id in(select char_id from user_data)
DELETE FROM char_pet WHERE char_id in(select char_id from user_data)
DELETE FROM ch3_lotto_char WHERE char_id in(select char_id from user_data)
DELETE FROM bookmark WHERE char_id in(select char_id from user_data)
DELETE FROM user_data WHERE char_id in(select char_id from user_data)
Проверка итема на две стопки, обычно при попытке дюпа или ошибки сервера:
SELECT i1.char_id, u.char_name, i1.amount as qty1, i2.amount as qty2, i1.item_idFROM user_item i1 (nolock), user_item i2 (nolock), user_data u (nolock)
WHERE i1.item_type=57 and i2.item_type=57
and i1.char_id=i2.char_id and i1.char_id <> 0
and i1.warehouse=i2.warehouse
and i1.item_id < i2.item_id
and u.char_id=i1.char_id
and u.temp_delete_date IS NULL
ORDER BY i1.char_id

Проверка итема на две стопки, обычно при попытке дюпа или ошибки сервера:
SELECT i1.char_id, u.char_name, i1.amount as qty1, i2.amount as qty2, i1.item_idFROM user_item i1 (nolock), user_item i2 (nolock), user_data u (nolock)
WHERE i1.item_type=57 and i2.item_type=57
and i1.char_id=i2.char_id and i1.char_id <> 0
and i1.warehouse=i2.warehouse
and i1.item_id < i2.item_id
and u.char_id=i1.char_id
and u.temp_delete_date IS NULL
ORDER BY i1.char_id

Итем у всех чаров:
use lin2world
select ud.char_name, ui.amount, ud.create_date, ui.item_id, ui.char_id from user_item ui, user_data ud where ui.char_id=ud.char_id and ui.item_type=57 order by amount desc

Удаление ивентовых вещей:
//тыквыDELETE FROM user_item WHERE item_type=6389
DELETE FROM user_item WHERE item_type=6391
DELETE FROM user_item WHERE item_type=9390


//кубики
DELETE FROM user_item WHERE item_type=3887
DELETE FROM user_item WHERE item_type=3886
DELETE FROM user_item WHERE item_type=3885
DELETE FROM user_item WHERE item_type=3884
DELETE FROM user_item WHERE item_type=3883
DELETE FROM user_item WHERE item_type=3882
DELETE FROM user_item WHERE item_type=3888
DELETE FROM user_item WHERE item_type=3881
DELETE FROM user_item WHERE item_type=3880
DELETE FROM user_item WHERE item_type=3879
DELETE FROM user_item WHERE item_type=3878
DELETE FROM user_item WHERE item_type=3877
DELETE FROM user_item WHERE item_type=3876
DELETE FROM user_item WHERE item_type=3875


//сердца
DELETE FROM user_item WHERE item_type=4217
DELETE FROM user_item WHERE item_type=4216
DELETE FROM user_item WHERE item_type=4215
DELETE FROM user_item WHERE item_type=4214
DELETE FROM user_item WHERE item_type=4213
DELETE FROM user_item WHERE item_type=4212
DELETE FROM user_item WHERE item_type=4211
DELETE FROM user_item WHERE item_type=4210
DELETE FROM user_item WHERE item_type=4209


//медали
DELETE FROM user_item WHERE item_type=6401
DELETE FROM user_item WHERE item_type=6400
DELETE FROM user_item WHERE item_type=6399
DELETE FROM user_item WHERE item_type=6402
DELETE FROM user_item WHERE item_type=6393
DELETE FROM user_item WHERE item_type=6392
DELETE FROM user_item WHERE item_type=7058




//ng
DELETE FROM user_item WHERE item_type=5560
DELETE FROM user_item WHERE item_type=5561
DELETE FROM user_item WHERE item_type=5234
DELETE FROM user_item WHERE item_type=5283
DELETE FROM user_item WHERE item_type=4411
DELETE FROM user_item WHERE item_type=4412
DELETE FROM user_item WHERE item_type=4413
DELETE FROM user_item WHERE item_type=4414
DELETE FROM user_item WHERE item_type=4415
DELETE FROM user_item WHERE item_type=4416
DELETE FROM user_item WHERE item_type=4417
DELETE FROM user_item WHERE item_type=5956
DELETE FROM user_item WHERE item_type=5562
DELETE FROM user_item WHERE item_type=5563
DELETE FROM user_item WHERE item_type=5564
DELETE FROM user_item WHERE item_type=5565
DELETE FROM user_item WHERE item_type=5566
DELETE FROM user_item WHERE item_type=5583
DELETE FROM user_item WHERE item_type=5584
DELETE FROM user_item WHERE item_type=5585
DELETE FROM user_item WHERE item_type=5586
DELETE FROM user_item WHERE item_type=5555
DELETE FROM user_item WHERE item_type=5556
DELETE FROM user_item WHERE item_type=5557
DELETE FROM user_item WHERE item_type=5558
DELETE FROM user_item WHERE item_type=5559

Заменить у всех один итем на другой:
UPDATE user_item SET item_type=6656 WHERE item_type=9720

Если у кого то будет ошибка отсутствия базы для логов:
USE [lin2log]GO
/****** Object: Table [dbo].[log_insert] Script Date: 03/01/2007 23:51:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[log_insert](
[log_file] [nvarchar](255) COLLATE Korean_Wansung_CI_AS NOT NULL,
[log_table] [nvarchar](50) COLLATE Korean_Wansung_CI_AS NOT NULL,
[rowsprocessed] [int] NOT NULL,
[log_year] [int] NOT NULL,
[log_month] [int] NOT NULL,
[log_day] [int] NOT NULL,
[log_hour] [int] NOT NULL,
[log_ip] [int] NOT NULL,
[log_svr] [nvarchar](20) COLLATE Korean_Wansung_CI_AS NOT NULL,
[log_inout] [nvarchar](20) COLLATE Korean_Wansung_CI_AS NOT NULL,
[process_time] [int] NULL,
[inserted] [int] NULL
) ON [PRIMARY]

Скрипт на создание аккаунта:
INSERT INTO dbo.ssn(ssn,name,email,job,phone,zip,addr_main,addr_etc,account_num)VALUES ('_account_name_','_account_name_','odmin@gmail.com',0,'telphone','123456','','',1)
go
INSERT INTO user_account (account,pay_stat)
VALUES ('_account_name_', 1)
go
INSERT INTO user_auth (account,password,quiz1,quiz2,answer1,answer2)
VALUES ('_account_name_',0x355ADA81380381D174E659BC71EBCDCD,'lineage2','lineage2',0x355ADA81380381D174E659BC71EBCDCD,0x355ADA81380381D174E659BC71EBCDCD)
go
INSERT INTO user_info (account,ssn,kind)
VALUES ('_account_name_','_account_name_', 99)
go
UPDATE user_auth
SET password = 0x355ADA81380381D174E659BC71EBCDCD
WHERE account = '_account_name_'
go

Password: q1w2e3r4t5y6

И еще пару типов паролей:
root = 0xB1BE70E9A83F19192CB593935EC4E2E2
odmin = 0x2967DE64D95DD1D0E57C5A5A970D2B2B
mentos = 0x25688B73CEEF2D5A6FF6D0D01D87A1A1
123321 = 0xB53AA258654C76D6E37A5C5C910B2D2D
pasworrd = 0xB539450DB091EC8ABF266666CD577171
megoadmin = 0x25681B3ABB0C39DA6B18949558C2E4E4

Вайп двумя запросами:
use [lin2world]go
TRUNCATE TABLE dbo.user_warehouse
TRUNCATE TABLE dbo.Pledge
TRUNCATE TABLE dbo.war_declare
TRUNCATE TABLE dbo.Pledge_Crest
TRUNCATE TABLE dbo.QuestData
TRUNCATE TABLE dbo.SkillData
TRUNCATE TABLE dbo.TEST
TRUNCATE TABLE dbo.account_ch2
TRUNCATE TABLE dbo.agit
TRUNCATE TABLE dbo.agit_adena
TRUNCATE TABLE dbo.agit_auction
TRUNCATE TABLE dbo.agit_bid
TRUNCATE TABLE dbo.agit_deco
TRUNCATE TABLE dbo.bookmark
TRUNCATE TABLE dbo.builder_account
TRUNCATE TABLE dbo.castle
TRUNCATE TABLE dbo.castle_crop
TRUNCATE TABLE dbo.castle_tax
TRUNCATE TABLE dbo.castle_war
TRUNCATE TABLE dbo.ch2_temp
TRUNCATE TABLE dbo.ch3_lotto_char
TRUNCATE TABLE dbo.char_pet
TRUNCATE TABLE dbo.class_list
TRUNCATE TABLE dbo.control_tower
TRUNCATE TABLE dbo.door
TRUNCATE TABLE dbo.dt_adena_rank_temp
TRUNCATE TABLE dbo.err_item_1
TRUNCATE TABLE dbo.err_pet1_9_28
use [lin2db]go
TRUNCATE TABLE dbo.block_msg
TRUNCATE TABLE dbo.block_reason_code
TRUNCATE TABLE dbo.gm_illegal_login
TRUNCATE TABLE dbo.item_code
TRUNCATE TABLE dbo.reg_cache
TRUNCATE TABLE dbo.server
TRUNCATE TABLE dbo.ssn
TRUNCATE TABLE dbo.user_account
TRUNCATE TABLE dbo.user_auth
TRUNCATE TABLE dbo.user_count
TRUNCATE TABLE dbo.user_info
TRUNCATE TABLE dbo.user_time
TRUNCATE TABLE dbo.worldstatus

Более полная версия вайпа:
use [lin2world]go
TRUNCATE TABLE dbo.user_warehouse
TRUNCATE TABLE dbo.Pledge
TRUNCATE TABLE dbo.war_declare
TRUNCATE TABLE dbo.Pledge_Crest
TRUNCATE TABLE dbo.QuestData
TRUNCATE TABLE dbo.SkillData
TRUNCATE TABLE dbo.TEST
TRUNCATE TABLE dbo.account_ch2
TRUNCATE TABLE dbo.agit
TRUNCATE TABLE dbo.agit_adena
TRUNCATE TABLE dbo.agit_auction
TRUNCATE TABLE dbo.agit_bid
TRUNCATE TABLE dbo.agit_deco
TRUNCATE TABLE dbo.bookmark
TRUNCATE TABLE dbo.user_data
TRUNCATE TABLE dbo.user_data_moved
TRUNCATE TABLE dbo.user_data_temp
TRUNCATE TABLE dbo.user_deleted
TRUNCATE TABLE dbo.user_friend
TRUNCATE TABLE dbo.castle
TRUNCATE TABLE dbo.castle_crop
TRUNCATE TABLE dbo.castle_tax
TRUNCATE TABLE dbo.castle_war
TRUNCATE TABLE dbo.ch2_temp
TRUNCATE TABLE dbo.ch3_lotto_char
TRUNCATE TABLE dbo.char_pet
TRUNCATE TABLE dbo.class_list
TRUNCATE TABLE dbo.control_tower
TRUNCATE TABLE dbo.door
TRUNCATE TABLE dbo.dt_adena_rank_temp
TRUNCATE TABLE dbo.err_item_1
TRUNCATE TABLE dbo.err_pet1_9_28
TRUNCATE TABLE dbo.monrace
TRUNCATE TABLE dbo.monrace_mon
TRUNCATE TABLE dbo.monrace_ticket
TRUNCATE TABLE dbo.nobless_achievements
TRUNCATE TABLE dbo.nobless_achievements
TRUNCATE TABLE dbo.olympiad
TRUNCATE TABLE dbo.pet_data
TRUNCATE TABLE dbo.pledge_ext
TRUNCATE TABLE dbo.quest
TRUNCATE TABLE dbo.shortcut_data
TRUNCATE TABLE dbo.ssq_data
TRUNCATE TABLE dbo.ssq_join_data
TRUNCATE TABLE dbo.ssq_top_point_user
TRUNCATE TABLE dbo.time_attack_record
TRUNCATE TABLE dbo.time_attack_record_test
TRUNCATE TABLE dbo.time_data
TRUNCATE TABLE dbo.user_blocklist
TRUNCATE TABLE dbo.user_henna
TRUNCATE TABLE dbo.user_history
TRUNCATE TABLE dbo.user_item
TRUNCATE TABLE dbo.user_log
TRUNCATE TABLE dbo.user_macro
TRUNCATE TABLE dbo.user_macroinfo
TRUNCATE TABLE dbo.user_macroinfo
TRUNCATE TABLE dbo.user_newbie
TRUNCATE TABLE dbo.user_nobless
TRUNCATE TABLE dbo.user_recipe
TRUNCATE TABLE dbo.user_skill
TRUNCATE TABLE dbo.user_sociality
TRUNCATE TABLE dbo.user_sociality
TRUNCATE TABLE dbo.user_subjob

Скрипт откатывает все скиллы во время рестарта:
use lin2worldupdate dbo.user_skill Set to_end_time = 0 where to_end_time >1000
delete from dbo.user_ActiveSkill
delete from dbo.user_item where char_id=0

Сброс штрафа на вступление и прием в клан
use lin2worldupdate user_data set pledge_ousted_time=0 where pledge_dismiss_time>0
update user_data set pledge_dismiss_time=0 where pledge_dismiss_time>0
update user_data set pledge_withdraw_time=0 where pledge_withdraw_time>0
update Pledge set oust_time=0 where oust_time>0
update Pledge set dismiss_reserved_time=0 where dismiss_reserved_time>0
update Pledge set alliance_withdraw_time=0 where alliance_withdraw_time>0
update Pledge set alliance_dismiss_time=0 where alliance_dismiss_time>0

Бэкап (простой скрипт)(заменить f:\sqlbups на свой адрес папки с бэкапом, папку создать заранее!!):
DECLARE @BACKUP_NAME_lin2clancomm VARCHAR(8000)
DECLARE @BACKUP_NAME_lin2comm VARCHAR(8000)
DECLARE @BACKUP_NAME_lin2db VARCHAR(8000)
DECLARE @BACKUP_NAME_lin2report VARCHAR(8000)
DECLARE @BACKUP_NAME_lin2user VARCHAR(8000)
DECLARE @BACKUP_NAME_lin2world VARCHAR(8000)


SET @BACKUP_NAME_lin2clancomm = 'f:\sqlbups\lin2clancomm_'+CONVERT(VARCHAR, GETDATE(), 112)+'_'+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '_')+'.BAK'
SET @BACKUP_NAME_lin2comm = 'f:\sqlbups\lin2comm_'+CONVERT(VARCHAR, GETDATE(), 112)+'_'+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '_')+'.BAK'
SET @BACKUP_NAME_lin2db = 'f:\sqlbups\lin2db_'+CONVERT(VARCHAR, GETDATE(), 112)+'_'+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '_')+'.BAK'
SET @BACKUP_NAME_lin2report = 'f:\sqlbups\lin2report_'+CONVERT(VARCHAR, GETDATE(), 112)+'_'+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '_')+'.BAK'
SET @BACKUP_NAME_lin2user = 'f:\sqlbups\lin2user_'+CONVERT(VARCHAR, GETDATE(), 112)+'_'+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '_')+'.BAK'
SET @BACKUP_NAME_lin2world = 'f:\sqlbups\lin2world_'+CONVERT(VARCHAR, GETDATE(), 112)+'_'+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '_')+'.BAK'


BACKUP DATABASE lin2clancomm TO DISK = @BACKUP_NAME_lin2clancomm
BACKUP DATABASE lin2comm TO DISK = @BACKUP_NAME_lin2comm
BACKUP DATABASE lin2db TO DISK = @BACKUP_NAME_lin2db
BACKUP DATABASE lin2report TO DISK = @BACKUP_NAME_lin2report
BACKUP DATABASE lin2user TO DISK = @BACKUP_NAME_lin2user
BACKUP DATABASE lin2world TO DISK = @BACKUP_NAME_lin2world

Удаляем вещи:
USE lin2world;DELETE
FROM
user_item
WHERE
item_type IN (ИД_ПУХИ, ИД_ПУХИ2, ИД_ПУХИ3, ...);

Добавляем итем всем персонажем:
special gifts, anniversary events

DECLARE @item_type INT;
DECLARE @amount int;
SET @item_type = '57';
SET @amount = '1000000';


INSERT INTO user_item (char_id, item_type, amount, enchant, eroded, bless, ident, wished, warehouse)
SELECT char_id, @item_type, @amount, 0, 0, 0, 0, 0, 0
FROM user_data

Чистим базу от умерших чаров. Не забываем делать бекап.

Удаляем ненужных чаров:
<?php $db_host = 'Ваш хост';
$db_user = 'sa';
$db_pass = 'Пароль к бд';


function get_time($god, $month)
{
$time=$god.$month."01";
return $time;
}


echo "<center><form>Введите год: <input type='text' name='god'>&nbsp;&nbsp;&nbsp;Введите месяц: <input type='text' name='month'> (пример 05)&nbsp;&nbsp;&nbsp;<input type=submit value='Очистить'></form></center>";


if(@$_GET['god']<>'' && @$_GET['month']<>'')
{
mssql_connect($db_host, $db_user, $db_pass);
mssql_select_db('lin2world');


$sql=mssql_query("select * from user_data where login<'".get_time(@$_GET['god'], @$_GET['month'])."'");


if(mssql_num_rows($sql)>0)
{
while($top=mssql_fetch_array($sql))
{
if($top['pledge_id']=='0')
{
echo "<br>Удален чар с ником: ".$top['char_name']." . Последний раз заходил за чара: ".$top['login'];
mssql_query("delete from user_data where char_id='".$top['char_id']."'");
mssql_query("delete from user_item where char_id='".$top['char_id']."' and warehouse<>'2'");
mssql_query("delete from user_skill where char_id='".$top['char_id']."'");
mssql_query("delete from user_warehouse where char_id='".$top['char_id']."'");
mssql_query("delete from user_surrender where char_id='".$top['char_id']."'");
mssql_query("delete from user_subjob where char_id='".$top['char_id']."'");
mssql_query("delete from user_sociality where char_id='".$top['char_id']."'");
mssql_query("delete from user_skill_old where char_id='".$top['char_id']."'");
mssql_query("delete from user_recipe where char_id='".$top['char_id']."'");
mssql_query("delete from user_punish where char_id='".$top['char_id']."'");
mssql_query("delete from user_nobless where char_id='".$top['char_id']."'");
mssql_query("delete from user_newbie where char_id='".$top['char_id']."'");
mssql_query("delete from user_macro where char_id='".$top['char_id']."'");
mssql_query("delete from user_log where char_id='".$top['char_id']."'");
mssql_query("delete from user_journal where char_id='".$top['char_id']."'");
mssql_query("delete from user_history where char_id='".$top['char_id']."'");
mssql_query("delete from user_friend where char_id='".$top['char_id']."'");
mssql_query("delete from user_deleted where char_id='".$top['char_id']."'");
}
else
{
$ccc=mssql_query("select * from pledge where ruler_id='".$top['char_id']."'");


if(mssql_num_rows($ccc)==0)
{
echo "<br>Удален чар с ником: ".$top['char_name']." . Последний раз заходил за чара: ".$top['login'];
mssql_query("delete from user_data where char_id='".$top['char_id']."'");
mssql_query("delete from user_item where char_id='".$top['char_id']."' and warehouse<>'2'");
mssql_query("delete from user_skill where char_id='".$top['char_id']."'");
mssql_query("delete from user_warehouse where char_id='".$top['char_id']."'");
mssql_query("delete from user_surrender where char_id='".$top['char_id']."'");
mssql_query("delete from user_subjob where char_id='".$top['char_id']."'");
mssql_query("delete from user_sociality where char_id='".$top['char_id']."'");
mssql_query("delete from user_skill_old where char_id='".$top['char_id']."'");
mssql_query("delete from user_recipe where char_id='".$top['char_id']."'");
mssql_query("delete from user_punish where char_id='".$top['char_id']."'");
mssql_query("delete from user_nobless where char_id='".$top['char_id']."'");
mssql_query("delete from user_newbie where char_id='".$top['char_id']."'");
mssql_query("delete from user_macro where char_id='".$top['char_id']."'");
mssql_query("delete from user_log where char_id='".$top['char_id']."'");
mssql_query("delete from user_journal where char_id='".$top['char_id']."'");
mssql_query("delete from user_history where char_id='".$top['char_id']."'");
mssql_query("delete from user_friend where char_id='".$top['char_id']."'");
mssql_query("delete from user_deleted where char_id='".$top['char_id']."'");
}
else
{
$bbb=mssql_fetch_array($ccc);
echo "<br>Удален чар с ником: ".$top['char_name']." . Последний раз заходил за чара: ".$top['login']." . А так же удален клан: ".$bbb['name'];
mssql_query("update user_Data set pledge_id='0' where pledge_id='".$bbb['pledge_id']."'");
mssql_query("delete from pledge where ruler_id='".$top['char_id']."'");
mssql_query("delete from user_data where char_id='".$top['char_id']."'");
mssql_query("delete from user_item where char_id='".$top['char_id']."' and warehouse<>'2'");
mssql_query("delete from user_skill where char_id='".$top['char_id']."'");
mssql_query("delete from user_warehouse where char_id='".$top['char_id']."'");
mssql_query("delete from user_surrender where char_id='".$top['char_id']."'");
mssql_query("delete from user_subjob where char_id='".$top['char_id']."'");
mssql_query("delete from user_sociality where char_id='".$top['char_id']."'");
mssql_query("delete from user_skill_old where char_id='".$top['char_id']."'");
mssql_query("delete from user_recipe where char_id='".$top['char_id']."'");
mssql_query("delete from user_punish where char_id='".$top['char_id']."'");
mssql_query("delete from user_nobless where char_id='".$top['char_id']."'");
mssql_query("delete from user_newbie where char_id='".$top['char_id']."'");
mssql_query("delete from user_macro where char_id='".$top['char_id']."'");
mssql_query("delete from user_log where char_id='".$top['char_id']."'");
mssql_query("delete from user_journal where char_id='".$top['char_id']."'");
mssql_query("delete from user_history where char_id='".$top['char_id']."'");
mssql_query("delete from user_friend where char_id='".$top['char_id']."'");
mssql_query("delete from user_deleted where char_id='".$top['char_id']."'");
}
}
}


echo "<br>Удаление закончили....";
}
else
{
echo "Удалять, то нечего ) ";
}
}
else
{
echo "<br>Пожалуйста укажите год/месяц";
}
?>

Удаляем всех чаров:
DELETE FROM user_surrender WHERE char_id in(select char_id from user_data)DELETE FROM user_subjob WHERE char_id in(select char_id from user_data)
DELETE FROM user_sociality WHERE char_id in(select char_id from user_data)
DELETE FROM user_skill_old WHERE char_id in(select char_id from user_data)
DELETE FROM user_skill WHERE char_id in(select char_id from user_data)
DELETE FROM user_recipe WHERE char_id in(select char_id from user_data)
DELETE FROM user_punish WHERE char_id in(select char_id from user_data)
DELETE FROM user_nobless WHERE char_id in(select char_id from user_data)
DELETE FROM user_newbie WHERE char_id in(select char_id from user_data)
delete from user_macroinfo
where user_macroinfo.macro_id in (select distinct user_macro.macro_id
from user_macro join user_macroinfo on (user_macro.macro_id = user_macroinfo.macro_id)
where user_macro.char_id in(select char_id from user_data))
DELETE FROM user_macro WHERE char_id in(select char_id from user_data)
DELETE FROM user_log WHERE char_id in(select char_id from user_data)
DELETE FROM user_journal WHERE char_id in(select char_id from user_data)
DELETE FROM user_item WHERE char_id in(select char_id from user_data) AND warehouse BETWEEN 0 AND 1
DELETE FROM user_history WHERE char_id in(select char_id from user_data)
DELETE FROM user_henna WHERE char_id in(select char_id from user_data)
DELETE FROM user_friend WHERE char_id in(select char_id from user_data)
DELETE FROM user_deleted WHERE char_id in(select char_id from user_data)
DELETE FROM user_data_moved WHERE char_id in(select char_id from user_data)
DELETE FROM user_comment WHERE char_id in(select char_id from user_data)
DELETE FROM user_ActiveSkill WHERE char_id in(select char_id from user_data)
DELETE FROM ssq_user_data WHERE char_id in(select char_id from user_data)
DELETE FROM quest WHERE char_id in(select char_id from user_data)
DELETE FROM olympiad_result WHERE char_id in(select char_id from user_data)
DELETE FROM olympiad_match WHERE char_id in(select char_id from user_data)
DELETE FROM nobless_achievements WHERE char_id in(select char_id from user_data)
DELETE FROM char_pet WHERE char_id in(select char_id from user_data)
DELETE FROM ch3_lotto_char WHERE char_id in(select char_id from user_data)
DELETE FROM bookmark WHERE char_id in(select char_id from user_data)
DELETE FROM user_data WHERE char_id in(select char_id from user_data)

Удаляем все аккаунты:
USE lin2dbGO


TRUNCATE TABLE ssn
TRUNCATE TABLE user_account
TRUNCATE TABLE user_auth
TRUNCATE TABLE user_count
TRUNCATE TABLE user_info
TRUNCATE TABLE hauthd_log

Вайп сервера:
Код:
/**************************************************************************This script will delete data from every table in the database except
those specified in the @NoDeleteLst.


@NoDeleteLst =     Comma seperated list of all tables you DO NOT want this
        script to delete.
      
        Example:
        SET @NoDeleteLst = "Application,App_Parameter,Parameter"
      
        The above will keep the script from deleting from the
        Application,App_Parameter and Parameter tables.


NOTE:    SCROLL TO MIDDLE OF SCRIPT TO SET @NoDeleteLst.
***************************************************************************/


--Make sure fnSplit2 exists. If not...create it
if exists (select * from dbo.sysobjects where id = object_id(N"[dbo].[fnSplit2]") and xtype in (N"FN", N"IF", N"TF"))
drop function [dbo].[fnSplit2]
GO


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


CREATE FUNCTION fnSplit2(@sText varchar(8000), @sDelim varchar(20) = " ")
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
/********************************************************************************
*************************************
This function parses a delimited string and returns it as an ID"d table.


Parameter Definition:
---------------------------------
@sText = Delimited string to be parsed.
@sDelim = Delimitation character used to seperate list ov values.


RETURNS:
---------------------------
Returns the table defined below:


Column         Description
----------        ------------------
idx        ID column for array
value        Value split from list.


********************************************************************************
*************************************/
AS
BEGIN
DECLARE @idx smallint,
    @value varchar(8000),
    @bcontinue bit,
    @iStrike smallint,
    @iDelimlength tinyint


IF @sDelim = "Space"
    BEGIN
    SET @sDelim = " "
    END


SET @idx = 1
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1


IF NOT ((@iDelimlength = 0) or (@sDelim = "Empty"))
    BEGIN
    WHILE @bcontinue = 1
        BEGIN


--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.
        IF CHARINDEX(@sDelim, @sText)>0
            BEGIN
            SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
                BEGIN
                INSERT @retArray (idx, value)
                VALUES (@idx, @value)
                END
          
--Trim the element and its delimiter from the front of the string.
            --Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
            SET @idx = @idx + 1
            SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
      
            END
        ELSE
            BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
                BEGIN
                INSERT @retArray (idx, value)
                VALUES (@idx, @value)
                END
            --Exit the WHILE loop.
SET @bcontinue = 0
            END
        END
    END
ELSE
    BEGIN
    WHILE @bcontinue=1
        BEGIN
        --If the delimiter is an empty string, check for remaining text
        --instead of a delimiter. Insert the first character into the
        --retArray table. Trim the character from the front of the string.
--Increment the index and loop.
        IF DATALENGTH(@sText)>1
            BEGIN
            SET @value = SUBSTRING(@sText,1,1)
                BEGIN
                INSERT @retArray (idx, value)
                VALUES (@idx, @value)
                END
            SET @idx = @idx+1
            SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
          
            END
        ELSE
            BEGIN
            --One character remains.
            --Insert the character, and exit the WHILE loop.
            INSERT @retArray (idx, value)
            VALUES (@idx, @sText)
            SET @bcontinue = 0  
            END
    END


END


RETURN
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------


-------------------------------------------------------------------------------------------------------
--SET NO-DELETE LIST HERE (tbls you dont want to delete from) - Comma seperated list. "tbl1,tbl2,tbl3"
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
DECLARE @tbls TABLE(IDX int IDENTITY(1,1), Tbl varchar(255))
DECLARE @Tbl varchar(255)


INSERT INTO @tbls(Tbl)
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = "BASE TABLE"
AND LEFT(TABLE_NAME,2) <> "dt"


--Disable Constraints
PRINT "-----------------------------------------------" + CHAR(13) + CHAR(13)
DECLARE curDeleteDB CURSOR FOR
SELECT DISTINCT Tbl
FROM @tbls


OPEN curDeleteDB


FETCH NEXT FROM curDeleteDB
INTO @tbl


WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        PRINT "Disabling constraints/triggers for - " + @tbl
        EXEC("ALTER TABLE [" + @tbl + "] NOCHECK CONSTRAINT ALL")
        EXEC("ALTER TABLE [" + @tbl + "] DISABLE TRIGGER ALL")
    END
    FETCH NEXT FROM curDeleteDB
    INTO @tbl
END
CLOSE curDeleteDB
DEALLOCATE curDeleteDB


--Delete Data
PRINT "-----------------------------------------------" + CHAR(13) + CHAR(13)
DECLARE curDeleteDB CURSOR FOR
SELECT DISTINCT Tbl
FROM @tbls


OPEN curDeleteDB


FETCH NEXT FROM curDeleteDB
INTO @tbl


WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        PRINT "Deleting from - " + @tbl
        EXEC("DELETE FROM [" + @tbl + "]")


        --If table has IDENTITY column reset the seed
        IF EXISTS
        (
            SELECT * FROM INFORMATION_SCHEMA.COLUMNS
            WHERE COLUMNPROPERTY(OBJECT_ID("dbo." + @tbl) ,COLUMN_NAME,"IsIdentity") = 1
            AND TABLE_NAME = @tbl
        )
        BEGIN
            EXEC("DBCC CHECKIDENT (""" + @tbl + """, RESEED, 0)")
        END
    END
    FETCH NEXT FROM curDeleteDB
    INTO @tbl
END
CLOSE curDeleteDB
DEALLOCATE curDeleteDB


--Re-Enable Constraints
PRINT "-----------------------------------------------" + CHAR(13) + CHAR(13)
DECLARE curDeleteDB CURSOR FOR
SELECT DISTINCT Tbl
FROM @tbls


OPEN curDeleteDB


FETCH NEXT FROM curDeleteDB
INTO @tbl


WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        PRINT "Enabling constraints/triggers for - " + @tbl
        EXEC("ALTER TABLE [" + @tbl + "] CHECK CONSTRAINT ALL")
        EXEC("ALTER TABLE [" + @tbl + "] ENABLE TRIGGER ALL")
    END
    FETCH NEXT FROM curDeleteDB
    INTO @tbl
END
CLOSE curDeleteDB
DEALLOCATE curDeleteDB
SET NOCOUNT OFF

Бесконечные соски:
Код:
ALTER PROCEDURE [DBO].[lin_AmountChange] (
@char_id INT,
@item_id INT,
@change INT,
@item_type INT,
@bZeorDelete INT = 0
)
AS
SET NOCOUNT ON


DECLARE @nResultAmount INT
SET @nResultAmount = -1


IF(select top 1 amount from user_item where char_id = @char_id AND item_id = @item_id ) + @change >= 0
begin
select @item_type=item_type from user_item where item_id = @item_id
if @item_type in (1463, 1464, 1465, 1466, 1467,2510, 2511, 2512, 2513, 2514, 3948, 3949, 3950, 3951, 3952)
begin
SELECT 10
end
else begin
UPDATE user_item SET amount = amount + @change WHERE char_id = @char_id AND item_id = @item_id


IF NOT @@ROWCOUNT = 1
SELECT -1
ELSE
SELECT @nResultAmount = ISNULL(amount, -1) FROM user_item WHERE char_id = @char_id AND item_id = @item_id
IF ( @nResultAmount = 0 AND @bZeorDelete = 1)
BEGIN
DELETE user_item WHERE char_id = @char_id AND item_id = @item_id
-- UPDATE user_item SET char_id = 0, item_type = 0 WHERE char_id = @char_id AND item_id = @item_id
END


SELECT @nResultAmount
end
end
else
select -1
GO

Убираем лаг сосок:
lin2world_index_create.sql:
Код:
USE [lin2world]
GO
/****** Object:  StoredProcedure [dbo].[lin_Reindex]    Script Date: 10/07/2006 06:00:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[lin_Reindex]   
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor
END
lin2world_index_exec.sql:
Код:
DECLARE @RC int
EXECUTE @RC = [lin2world].[dbo].[lin_Reindex]
 
  • Мне нравится
Реакции: rikktor

    Mangol

    Баллов: 14
    Без комментариев

к мануалу есть 2 уточнения\улучшения

1. update user_account set pay_stat = 0 where account = 'ИМЯ АККАУНТА'

это не премиум аккаунт, это БАН аккаунта, через данную фичу обычно работает подтверждение аккаунта через почту, и пока pay_stat=0, зайти нельзя. У корейцев этот параметр использовался как ID оплаты аккаунты, 1 = оплачен и доступен, 0 - отключен

2. Универсальный скрипт для вайпа любой базы
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

через цикл вайпает все таблицы в базе
 
  • Мне нравится
Реакции: Grand и kick

    kick

    Баллов: 25
    За сообщение
Назад
Сверху Снизу