archgeus / LogDB_*

USE [LogDB_2800] GO /****** Object: StoredProcedure [dbo].[spCreateBcpResult] Script Date: 7/19/2022 8:22:02 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE [dbo].[spCreateBcpResult] -- Add the parameters for the stored procedure here @logFileName nvarchar(256), @movedFileName nvarchar(256), @bcpLines int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; insert into bcpResult( logFileName, movedFileName, bcpLines) values (@logFileName, @movedFileName, @bcpLines) END GO /****** Object: StoredProcedure [dbo].[spCreateBcpResult2] Script Date: 7/19/2022 8:22:02 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spCreateBcpResult2] -- Add the parameters for the stored procedure here @logFileName nvarchar(256), @movedFileName nvarchar(256), @bcpLines int, @execTime bigint AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME='bcpResult2') create table bcpResult2 ( bcpTime timestamp, logFileName nvarchar(256), movedFileName nvarchar(256), bcpLines int, execTime bigint) insert into bcpResult2( logFileName, movedFileName, bcpLines, execTime) values (@logFileName, @movedFileName, @bcpLines, @execTime) END GO /****** Object: StoredProcedure [dbo].[spCreateLogTableChat] Script Date: 7/19/2022 8:22:02 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spCreateLogTableChat] @logTableName NVARCHAR(128) AS BEGIN SET NOCOUNT ON; DECLARE @sqlquery VARCHAR(1024); SET @sqlquery = 'if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME=''' + @logTableName + ''') \ begin \ create table ' + @logTableName + '( \ timeStamp datetime,\ planetId int,\ serverType int,\ logType int,\ intVal1 int, intVal2 int, intVal3 int, intVal4 int, intVal5 int, intVal6 int, intVal7 int, intVal8 int, intVal9 int, intVal10 int, intVal11 int, intVal12 int, intVal13 int, intVal14 int, intVal15 int,\ int64Val1 bigint, int64Val2 bigint, int64Val3 bigint, int64Val4 bigint,\ itemDbId bigint,\ contId int, channelId int, posX int, posY int, posZ int, \ stringVal1 nvarchar(128), stringVal2 nvarchar(128), stringVal3 nvarchar(128), stringVal4 nvarchar(128) , chat nvarchar(516) ) CREATE CLUSTERED INDEX IX_' + @logTableName + ' ON dbo.' + @logTableName + '( \ logType, intVal1 ) \ end '; EXEC (@sqlquery); END GO /****** Object: StoredProcedure [dbo].[spCreateLogTableChat_Old] Script Date: 7/19/2022 8:22:02 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spCreateLogTableChat_Old] @logTableName NVARCHAR(128) AS BEGIN SET NOCOUNT ON; DECLARE @sqlquery VARCHAR(1024); SET @sqlquery = 'if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME=''' + @logTableName + ''') \ begin \ create table ' + @logTableName + '( \ timeStamp datetime,\ planetId int,\ serverType int,\ logType int,\ intVal1 int, intVal2 int, intVal3 int, intVal4 int, intVal5 int, intVal6 int, intVal7 int, intVal8 int, intVal9 int, intVal10 int,\ doubleVal1 float, doubleVal2 float, doubleVal3 float, doubleVal4 float,\ int64Val1 bigint, int64Val2 bigint,\ itemDbId bigint,\ contId int, channelId int, posX int, posY int, posZ int, \ stringVal1 nvarchar(128), stringVal2 nvarchar(128), stringVal3 nvarchar(128), stringVal4 nvarchar(128) , chat nvarchar(516) ) CREATE CLUSTERED INDEX IX_' + @logTableName + ' ON dbo.' + @logTableName + '( \ logType, intVal1 ) \ end '; EXEC (@sqlquery); END GO /****** Object: StoredProcedure [dbo].[spCreateLogTableEx] Script Date: 7/19/2022 8:22:02 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* 로그 테이블 만들기 프로시져 생성 */ CREATE PROCEDURE [dbo].[spCreateLogTableEx] @logTableName NVARCHAR(128) AS BEGIN SET NOCOUNT ON; DECLARE @sqlquery VARCHAR(2048); SET @sqlquery = 'if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME=''' + @logTableName + ''') \ begin \ create table ' + @logTableName + '( \ timeStamp datetime,\ planetId int,\ serverType int,\ logType int,\ intVal1 int, intVal2 int, intVal3 int, intVal4 int, intVal5 int, intVal6 int, intVal7 int, intVal8 int, intVal9 int, intVal10 int, intVal11 int, intVal12 int, intVal13 int, intVal14 int, intVal15 int,\ int64Val1 bigint, int64Val2 bigint, int64Val3 bigint, int64Val4 bigint,\ itemDbId bigint,\ contId int, channelId int, posX int, posY int, posZ int, \ stringVal1 nvarchar(128), stringVal2 nvarchar(128), stringVal3 nvarchar(128), stringVal4 nvarchar(128)) \ CREATE CLUSTERED INDEX IX_' + @logTableName + ' ON dbo.' + @logTableName + ' (timeStamp, stringVal1, logType) \ CREATE NONCLUSTERED INDEX IX2_' + @logTableName + ' ON dbo.' + @logTableName + ' (itemDbId, timeStamp, logType) \ end '; /* CREATE CLUSTERED INDEX IX_' + @logTableName + ' ON dbo.' + @logTableName + ' (logType, intVal1, intVal2, intVal3 ) \ CREATE NONCLUSTERED INDEX IX2_' + @logTableName + ' ON dbo.' + @logTableName + ' (stringVal1) \ CREATE NONCLUSTERED INDEX IX3_' + @logTableName + ' ON dbo.' + @logTableName + ' (itemDbId) where (itemDbId <> 0) \ */ EXEC (@sqlquery); END GO /****** Object: StoredProcedure [dbo].[spCreateLogTableEx_Old] Script Date: 7/19/2022 8:22:02 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spCreateLogTableEx_Old] @logTableName NVARCHAR(128) AS BEGIN SET NOCOUNT ON; DECLARE @sqlquery VARCHAR(1024); SET @sqlquery = 'if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME=''' + @logTableName + ''') \ begin \ create table ' + @logTableName + '( \ timeStamp datetime,\ planetId int,\ serverType int,\ logType int,\ intVal1 int, intVal2 int, intVal3 int, intVal4 int, intVal5 int, intVal6 int, intVal7 int, intVal8 int, intVal9 int, intVal10 int,\ doubleVal1 float, doubleVal2 float, doubleVal3 float, doubleVal4 float,\ int64Val1 bigint, int64Val2 bigint,\ itemDbId bigint,\ contId int, channelId int, posX int, posY int, posZ int, \ stringVal1 nvarchar(128), stringVal2 nvarchar(128), stringVal3 nvarchar(128), stringVal4 nvarchar(128)) \ CREATE CLUSTERED INDEX IX_' + @logTableName + ' ON dbo.' + @logTableName + '( \ logType, intVal1, intVal2, intVal3 ) end '; EXEC (@sqlquery); END GO /****** Object: StoredProcedure [dbo].[spDeleteOldLogTable] Script Date: 7/19/2022 8:22:02 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE [dbo].[spDeleteOldLogTable] @daysBefore int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE tables_cursor CURSOR FOR select name from sys.objects (nolock) WHERE type='U' and create_date < dateadd(day, @daysBefore, GETUTCDATE()) and ( name like 'GAMELOG%' or name like 'CHATLOG%' or name like 'AUDITLOG%' ) OPEN tables_cursor DECLARE @tablename sysname FETCH NEXT FROM tables_cursor INTO @tablename WHILE (@@FETCH_STATUS <> -1) BEGIN EXEC ( 'drop table ' + @tablename) -- EXEC ( 'select ''' + @tablename + ''' ') FETCH NEXT FROM tables_cursor INTO @tablename END DEALLOCATE tables_cursor END GO /****** Object: StoredProcedure [dbo].[spGetAssertLogs] Script Date: 7/19/2022 8:22:02 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[spGetAssertLogs] as begin set nocount on; declare @lastTime datetime; set @lastTime = (select fetchtime from AssertLogLastFetchedInfo); declare @curTime datetime; set @curTime = GETUTCDATE(); delete from AssertLogLastFetchedInfo; insert into AssertLogLastFetchedInfo (fetchtime) values (@curTime); select timeStamp, planetId, serverType, stringVal1, stringVal2 from AssertLog where timeStamp >= @lastTime and timeStamp < @curTime; end GO /****** Object: StoredProcedure [dbo].[spLogDirect] Script Date: 7/19/2022 8:22:02 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[spLogDirect] @timeStamp datetime, @planetId int, @serverType int, @logType int, @intVal1 int, @intVal2 int, @intVal3 int, @intVal4 int, @intVal5 int, @intVal6 int, @intVal7 int, @intVal8 int, @intVal9 int, @intVal10 int, @intVal11 int, @intVal12 int, @intVal13 int, @intVal14 int, @intVal15 int, @int64Val1 bigint, @int64Val2 bigint, @int64Val3 bigint, @int64Val4 bigint, @stringVal1 nvarchar(128), @stringVal2 nvarchar(128), @stringVal3 nvarchar(128), @stringVal4 nvarchar(128) as begin set nocount on; insert into AssertLog ( timeStamp, planetId, serverType, logType, intVal1, intVal2, intVal3, intVal4, intVal5, intVal6, intVal7, intVal8, intVal9, intVal10, intVal11, intVal12, intVal13, intVal14, intVal15, int64Val1, int64Val2, int64Val3, int64Val4, stringVal1, stringVal2, stringVal3, stringVal4 ) values ( @timeStamp, @planetId, @serverType, @logType, @intVal1, @intVal2, @intVal3, @intVal4, @intVal5, @intVal6, @intVal7, @intVal8, @intVal9, @intVal10, @intVal11, @intVal12, @intVal13, @intVal14, @intVal15, @int64Val1, @int64Val2, @int64Val3, @int64Val4, @stringVal1, @stringVal2, @stringVal3, @stringVal4 ); end GO