[PTS] Полезные Sql запросы

[Cache]

Под официальный сервер Lineage 2 подобной темы здесь на нашёл, а ведь может пригодиться.

Резервная копия данных

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
 
Code: 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
 
Code: 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
 
Code: SQL

Персонажи создаются сразу 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
 
Code: 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
 
Code: SQL

Заменить один предмет (9720) на другой (6656) у всех игроков

UPDATE user_item SET item_type = 6656 WHERE item_type = 9720
 
Code: 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
 
Code: 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)
 
Code: 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
 
Code: 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
 
Code: SQL

Если у вас отсутствует база lin2log

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]
 
Code: SQL

Вайп

USE [lin2world]
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
 
Code: SQL

(с) RuleZzz, papko, KirillDE