01-19-2016, 12:32 PM
Под официальный сервер Lineage 2 подобной темы здесь на нашёл, а ведь может пригодиться.
Резервная копия данных
[src=sql]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 = 'C:\Backup\lin2clancomm_'+CONVERT(VARCHAR, GETDATE(), 112)+'_'+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '_')+'.BAK'
SET @BACKUP_NAME_lin2comm = 'C:\Backup\lin2comm_'+CONVERT(VARCHAR, GETDATE(), 112)+'_'+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '_')+'.BAK'
SET @BACKUP_NAME_lin2db = 'C:\Backup\lin2db_'+CONVERT(VARCHAR, GETDATE(), 112)+'_'+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '_')+'.BAK'
SET @BACKUP_NAME_lin2report = 'C:\Backup\lin2report_'+CONVERT(VARCHAR, GETDATE(), 112)+'_'+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '_')+'.BAK'
SET @BACKUP_NAME_lin2user = 'C:\Backup\lin2user_'+CONVERT(VARCHAR, GETDATE(), 112)+'_'+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '_')+'.BAK'
SET @BACKUP_NAME_lin2world = 'C:\Backup\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
[/src]
Сброс штрафа на приём и вступление в клан
[src=sql]USE lin2world
UPDATE 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
[/src]
На тот случай, если у игроков зависают скиллы
[src=sql]USE lin2world
UPDATE 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
[/src]
Персонажи создаются сразу 76 уровня
[src=sql]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
[/src]
Добавить предмет всем игрокам
[src=sql]DECLARE @item_type INT;
DECLARE @amount int;
SET @item_type = '57'; -- itemId
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
[/src]
Заменить один предмет (9720) на другой (6656) у всех игроков
[src=sql]UPDATE user_item SET item_type = 6656 WHERE item_type = 9720
[/src]
Удалить ивентовые предметы
[src=sql]//тыквы
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
[/src]
Удалить всех чаров
[src=sql]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)
[/src]
Получить определённый итем у всех чаров
[src=sql]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
[/src]
Проверка итемов на две стопки
[src=sql]SELECT i1.char_id, u.char_name, i1.amount as qty1, i2.amount as qty2, i1.item_id
FROM 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
[/src]
Если у вас отсутствует база lin2log
[src=sql]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]
[/src]
Вайп
[src=sql]USE [lin2world]
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
[/src]
(с) RuleZzz, papko, KirillDE
Резервная копия данных
[src=sql]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 = 'C:\Backup\lin2clancomm_'+CONVERT(VARCHAR, GETDATE(), 112)+'_'+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '_')+'.BAK'
SET @BACKUP_NAME_lin2comm = 'C:\Backup\lin2comm_'+CONVERT(VARCHAR, GETDATE(), 112)+'_'+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '_')+'.BAK'
SET @BACKUP_NAME_lin2db = 'C:\Backup\lin2db_'+CONVERT(VARCHAR, GETDATE(), 112)+'_'+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '_')+'.BAK'
SET @BACKUP_NAME_lin2report = 'C:\Backup\lin2report_'+CONVERT(VARCHAR, GETDATE(), 112)+'_'+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '_')+'.BAK'
SET @BACKUP_NAME_lin2user = 'C:\Backup\lin2user_'+CONVERT(VARCHAR, GETDATE(), 112)+'_'+REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '_')+'.BAK'
SET @BACKUP_NAME_lin2world = 'C:\Backup\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
[/src]
Сброс штрафа на приём и вступление в клан
[src=sql]USE lin2world
UPDATE 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
[/src]
На тот случай, если у игроков зависают скиллы
[src=sql]USE lin2world
UPDATE 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
[/src]
Персонажи создаются сразу 76 уровня
[src=sql]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
[/src]
Добавить предмет всем игрокам
[src=sql]DECLARE @item_type INT;
DECLARE @amount int;
SET @item_type = '57'; -- itemId
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
[/src]
Заменить один предмет (9720) на другой (6656) у всех игроков
[src=sql]UPDATE user_item SET item_type = 6656 WHERE item_type = 9720
[/src]
Удалить ивентовые предметы
[src=sql]//тыквы
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
[/src]
Удалить всех чаров
[src=sql]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)
[/src]
Получить определённый итем у всех чаров
[src=sql]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
[/src]
Проверка итемов на две стопки
[src=sql]SELECT i1.char_id, u.char_name, i1.amount as qty1, i2.amount as qty2, i1.item_id
FROM 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
[/src]
Если у вас отсутствует база lin2log
[src=sql]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]
[/src]
Вайп
[src=sql]USE [lin2world]
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
[/src]
(с) RuleZzz, papko, KirillDE