[MANUAL] Полезные MsSQL запросы

Тема в разделе "Установка и настройка (Setup and configuration)", создана пользователем root, 29 сен 2014.

Метки:
  1. root

    root Administrator Команда форума Administrator Moderator Developers Team

    Регистрация:
    23 авг 2014
    Сообщения:
    253
    Симпатии:
    56
    Баллы:
    11
    Делаем аккаунт премиум:
    Код:
    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
    Итем у всех чаров:
    Код:
    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_','[email protected]',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
     
  2. root

    root Administrator Команда форума Administrator Moderator Developers Team

    Регистрация:
    23 авг 2014
    Сообщения:
    253
    Симпатии:
    56
    Баллы:
    11
    Удаляем вещи:
    Код:
    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
     
  3. root

    root Administrator Команда форума Administrator Moderator Developers Team

    Регистрация:
    23 авг 2014
    Сообщения:
    253
    Симпатии:
    56
    Баллы:
    11
    Удаляем ненужных чаров mssql

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

    Удаляем ненужных чаров:
    Код:
    <?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>Пожалуйста укажите год/месяц";  
    }  
    ?>
     
  4. root

    root Administrator Команда форума Administrator Moderator Developers Team

    Регистрация:
    23 авг 2014
    Сообщения:
    253
    Симпатии:
    56
    Баллы:
    11
    Удаляем всех чаров:
    Код:
    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
     
  5. Demort

    Demort Developers Team Developers Team

    Регистрация:
    6 сен 2014
    Сообщения:
    9
    Симпатии:
    3
    Баллы:
    3
    Вайп

    Код:
    EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
     
  6. alks

    alks New Member User

    Регистрация:
    12 сен 2017
    Сообщения:
    1
    Симпатии:
    0
    Баллы:
    1
    Пол:
    Мужской
    а есть информация как пароли кодируются? Чтобы свой создать. Пробовал программой l2AC, но она к базе sql не цепляется, хотя все правильно вроде заполняю