Code:
USE [PS_GameLog]
GO
/****** Object: StoredProcedure [dbo].[uxp_CreateChatDB] Script Date: 02/12/2011 17:12:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object: Stored Procedure dbo.uxp_CreateChatDB Script Date: 2008-6-7 18:32:40 ******/
/****** Object: Stored Procedure dbo.uxp_CreateChatDB Script Date: 2008/3/15 下午 05:16:53 ******/
CREATE Proc [dbo].[uxp_CreateChatDB]
/*
Created by humanws, 2005-10-14
채팅로그DB 월별로 생성하는 프로시저
*/
@Dest1 varchar(200), -- 맨 마지막에 \ 까지 입력
@Dest2 varchar(200), -- 맨 마지막에 \ 까지 입력
@DBName varchar(50) = 'PS_ChatLog',
@DBDate datetime,
@ServerName varchar(50) = ''
AS
DECLARE @Sql1 varchar(8000)
DECLARE @Sql2 varchar(8000)
DECLARE @Sql3 varchar(8000)
DECLARE @Sql4 varchar(8000)
DECLARE @Sql5 varchar(8000)
DECLARE @Sql6 varchar(8000)
DECLARE @Sql7 varchar(8000)
DECLARE @Sql8 varchar(8000)
DECLARE @Sql9 varchar(8000)
DECLARE @Sql10 varchar(8000)
DECLARE @SqlAll varchar(8000)
DECLARE @StDate datetime
DECLARE @EnDate datetime
DECLARE @DayMax int
DECLARE @Cnt int
DECLARE @DBDateC varchar(6)
DECLARE @YYYY varchar(4)
DECLARE @MM varchar(2)
DECLARE @DD int
DECLARE @DDc varchar(2)
DECLARE @Result varchar(8000)
-- 필수변수 셋팅
SET @Cnt = 1
-- 서버 이름 셋팅 : 없으면 @@servername 으로
IF(@ServerName = '' OR @ServerName IS NULL)
BEGIN
SET @ServerName = (SELECT @@servername)
END
-- DB 날짜 셋팅
SET @YYYY = CAST ( DATEPART(yyyy, @DBDate) AS varchar(4) )
SET @MM = CAST ( DATEPART(mm, @DBDate) AS varchar(2) )
IF( LEN (@MM) = 1 )
BEGIN
SET @MM = '0' + @MM
END
-- 마지막날 구하기
SET @StDate = CAST(@YYYY + '-' + @MM + '-01' AS datetime)
SET @EnDate = DATEADD ( mm , 1, @StDate )
SET @EnDate = DATEADD ( dd , -1, @EnDate )
SET @DayMax = DATEPART(dd, @EnDate)
-- DBName 다시 설정 = DBName_YYYYMM
SET @DBName = @DBName + '_' + @YYYY + @MM
-- CREATE DATABASE 시작부분 / PRIMARY 까지
--[ServerName]__[CatalogName]_[Filegroup].m(n)df
SET @Sql1 =
'
CREATE DATABASE [' + @DBName + ']
ON
PRIMARY
( NAME = ' + @DBName + '_PRIMARY,
FILENAME = ''' + @dest1 + @ServerName + '__' + @DBName + '_PRIMARY.mdf'',
SIZE = 10MB,
FILEGROWTH = 10MB )
'
-- @Sql2 = Filegroup Model Script
SET @Sql2 =
'
, FILEGROUP FG[DD]
( NAME = ' + @DBName + '_FG[DD],
FILENAME = ''' + @dest1 + @ServerName + '__' + @DBName + '_FG[DD].ndf'',
SIZE = 10MB,
FILEGROWTH = 10MB
)
'
-- 저장되는 파라미터 초기화
SET @Sql4 = ''
-- WHILE 돌려서 파일그룹 얻어냄
WHILE ( @Cnt <= @DayMax )
BEGIN
SET @DDc = CAST ( @Cnt AS varchar(2) )
IF( LEN ( @DDc ) = 1 )
BEGIN
SET @DDc = '0' + @DDc
END
SET @Sql3 = @Sql2
SET @Sql3 = REPLACE(@Sql3, '[DD]', @DDc)
SET @Sql4 = @Sql4 + @Sql3
SET @Cnt = @Cnt + 1
END
-- 로그 설정
SET @Sql5 =
'
LOG ON
( NAME = ' + @DBName + '_Log,
FILENAME = ''' + @dest2 + @ServerName + '__' + @DBName + '_Log.ldf'',
SIZE = 10MB,
FILEGROWTH = 10MB
)
'
EXEC (@Sql1 + @Sql4 + @Sql5)
DECLARE @From varchar(100)
DECLARE @To varchar(100)
DECLARE @Subject varchar(100)
DECLARE @Body varchar(100)
SET @From = 'sqlagent_' + @servername + '@sonov.com'
SET @To = '[Only registered and activated users can see links. Click Here To Register...]'
SET @Subject = '[알림] ' + @DBName + ' 데이터베이스 생성 완료'
SET @Body = '[알림] ' + @DBName + ' 데이터베이스 생성 완료, 결과를 확인하세요.'
GO
/****** Object: StoredProcedure [dbo].[uxp_CreateDB] Script Date: 02/12/2011 17:12:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object: Stored Procedure dbo.uxp_CreateDB Script Date: 2008-6-7 18:32:40 ******/
/****** Object: Stored Procedure dbo.uxp_CreateDB Script Date: 2008/3/15 下午 05:16:53 ******/
CREATE Proc [dbo].[uxp_CreateDB]
/*
Created by [Only registered and activated users can see links. Click Here To Register...], 2004-09-13
게임로그 DB 월별로 생성하는 프로시저
이 프로시저를 실행시킬려면 sa권한이 있어야 합니다.
*/
@Dest1 varchar(200), -- 맨 마지막에 \ 까지 입력
@Dest2 varchar(200), -- 맨 마지막에 \ 까지 입력
@DBName varchar(50) = 'PS_GameLog',
@DBDate datetime,
@ServerName varchar(50) --= ''
AS
DECLARE @Sql1 varchar(8000)
DECLARE @Sql2 varchar(8000)
DECLARE @Sql3 varchar(8000)
DECLARE @Sql4 varchar(8000)
DECLARE @Sql5 varchar(8000)
DECLARE @Sql6 varchar(8000)
DECLARE @Sql7 varchar(8000)
DECLARE @Sql8 varchar(8000)
DECLARE @Sql9 varchar(8000)
DECLARE @Sql10 varchar(8000)
DECLARE @SqlAll varchar(8000)
DECLARE @StDate datetime
DECLARE @EnDate datetime
DECLARE @DayMax int
DECLARE @Cnt int
DECLARE @DBDateC varchar(6)
DECLARE @YYYY varchar(4)
DECLARE @MM varchar(2)
DECLARE @DD int
DECLARE @DDc varchar(2)
DECLARE @Result varchar(8000)
-- 필수변수 셋팅
SET @Cnt = 1
-- 서버 이름 셋팅 : 없으면 @@servername 으로
IF(@ServerName = '' OR @ServerName IS NULL)
BEGIN
SET @ServerName = (SELECT @@servername)
END
-- DB 날짜 셋팅
SET @YYYY = CAST ( DATEPART(yyyy, @DBDate) AS varchar(4) )
SET @MM = CAST ( DATEPART(mm, @DBDate) AS varchar(2) )
IF( LEN (@MM) = 1 )
BEGIN
SET @MM = '0' + @MM
END
-- 마지막날 구하기
SET @StDate = CAST(@YYYY + '-' + @MM + '-01' AS datetime)
SET @EnDate = DATEADD ( mm , 1, @StDate )
SET @EnDate = DATEADD ( dd , -1, @EnDate )
SET @DayMax = DATEPART(dd, @EnDate)
-- DBName 다시 설정 = DBName_YYYYMM
SET @DBName = @DBName + '_' + @YYYY + @MM
-- CREATE DATABASE 시작부분 / PRIMARY 까지
--[ServerName]__[CatalogName]_[Filegroup].m(n)df
SET @Sql1 =
'
CREATE DATABASE [' + @DBName + ']
ON
PRIMARY
( NAME = ' + @DBName + '_PRIMARY,
FILENAME = ''' + @dest1 + @ServerName + '__' + @DBName + '_PRIMARY.mdf'',
SIZE = 2MB,
FILEGROWTH = 2MB )
'
-- @Sql2 = Filegroup Model Script
SET @Sql2 =
'
, FILEGROUP FG[DD]
( NAME = ' + @DBName + '_FG[DD],
FILENAME = ''' + @dest1 + @ServerName + '__' + @DBName + '_FG[DD].ndf'',
SIZE = 2MB,
FILEGROWTH = 2MB
)
'
-- 저장되는 파라미터 초기화
SET @Sql4 = ''
-- WHILE 돌려서 파일그룹 얻어냄
WHILE ( @Cnt <= @DayMax )
BEGIN
SET @DDc = CAST ( @Cnt AS varchar(2) )
IF( LEN ( @DDc ) = 1 )
BEGIN
SET @DDc = '0' + @DDc
END
SET @Sql3 = @Sql2
SET @Sql3 = REPLACE(@Sql3, '[DD]', @DDc)
SET @Sql4 = @Sql4 + @Sql3
SET @Cnt = @Cnt + 1
END
-- 로그 설정
SET @Sql5 =
'
LOG ON
( NAME = ' + @DBName + '_Log,
FILENAME = ''' + @dest2 + @ServerName + '__' + @DBName + '_Log.ldf'',
SIZE = 2MB,
FILEGROWTH = 2MB
)
'
EXEC (@Sql1 + @Sql4 + @Sql5)
DECLARE @From varchar(100)
DECLARE @To varchar(100)
DECLARE @Subject varchar(100)
DECLARE @Body varchar(100)
SET @From = 'sqlagent_' + @servername + '@sonov.com'
SET @To = '[Only registered and activated users can see links. Click Here To Register...]'
SET @Subject = '[알림] ' + @DBName + ' 데이터베이스 생성 완료'
SET @Body = '[알림] ' + @DBName + ' 데이터베이스 생성 완료, 결과를 확인하세요.'
GO
/****** Object: StoredProcedure [dbo].[uxp_CreateTable] Script Date: 02/12/2011 17:12:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object: Stored Procedure dbo.uxp_CreateTable Script Date: 2008-6-7 18:32:40 ******/
/****** Object: Stored Procedure dbo.uxp_CreateTable Script Date: 2008/3/15 下午 05:16:53 ******/
CREATE Proc [dbo].[uxp_CreateTable]
/*
Created by [Only registered and activated users can see links. Click Here To Register...], 2004-09-14
게임로그 DB 일별로 테이블 만들어주는 쿼리
*/
@DBName varchar(50) = 'PS_GameLog',
@DBDate datetime
AS
-- 필수 내장 변수
DECLARE @SqlTbl varchar(8000)
DECLARE @SqlIdx varchar(8000)
DECLARE @Sql1 varchar(4000)
DECLARE @Sql2 varchar(4000)
DECLARE @Sql3 varchar(100)
DECLARE @StDate datetime
DECLARE @EnDate datetime
DECLARE @Cnt int
DECLARE @DayMax int
DECLARE @YYYY varchar(4)
DECLARE @MM varchar(2)
DECLARE @DD varchar(2)
DECLARE @Date1 datetime
DECLARE @Date2 datetime
DECLARE @Tempyyyy1 varchar(4)
DECLARE @Tempyyyy2 varchar(4)
DECLARE @Tempmm1 varchar(2)
DECLARE @Tempmm2 varchar(2)
DECLARE @Tempdd1 varchar(2)
DECLARE @Tempdd2 varchar(2)
DECLARE @Date1c varchar(30)
DECLARE @Date2c varchar(30)
-- 날짜 셋팅
SET @YYYY = CAST ( DATEPART(yyyy, @DBDate) AS varchar(4) )
SET @MM = CAST ( DATEPART(mm, @DBDate) AS varchar(2) )
IF( LEN (@MM) = 1 )
BEGIN
SET @MM = '0' + @MM
END
-- 마지막날 구하기
SET @StDate = CAST(@YYYY + '-' + @MM + '-01' AS datetime)
SET @EnDate = DATEADD ( mm , 1, @StDate )
SET @EnDate = DATEADD ( dd , -1, @EnDate )
SET @DayMax = DATEPART(dd, @EnDate)
-- DBName 다시 설정 = DBName_YYYYMM
SET @DBName = @DBName + '_' + @YYYY + @MM
-- DB 사용 스크립트
SET @Sql3 = 'USE ' + @DBName
-- 테이블 생성 스크립트 선언
SET @SqlTbl = '
CREATE TABLE dbo.ActionLog[DD] (
RowID bigint IDENTITY (1, 1) NOT NULL ,
UserID varchar (18) NOT NULL ,
UserUID int NOT NULL ,
CharID int NOT NULL ,
CharName varchar (50) NOT NULL ,
CharLevel tinyint NOT NULL ,
CharExp int NOT NULL ,
MapID smallint NOT NULL ,
PosX real NOT NULL ,
PosY real NOT NULL ,
PosZ real NOT NULL ,
ActionTime datetime NOT NULL ,
ActionType tinyint NOT NULL ,
Value1 bigint NULL ,
Value2 int NULL ,
Value3 int NULL ,
Value4 bigint NULL ,
Value5 int NULL ,
Value6 int NULL ,
Value7 int NULL ,
Value8 int NULL ,
Value9 int NULL ,
Value10 int NULL ,
Text1 varchar (100) NULL ,
Text2 varchar (100) NULL ,
Text3 varchar (100) NULL ,
Text4 varchar (100) NULL ,
CONSTRAINT PK_ActionLog[DD]_RowID PRIMARY KEY NONCLUSTERED
(
RowID
) ON FG[DD] ,
CONSTRAINT CK_ActionLog[DD]_ActionTime CHECK (ActionTime >= ''[Date1]'' AND ActionTime < ''[Date2]'' )
) ON FG[DD]
'
-- 인덱스 생성 스크립트 선언
SET @SqlIdx =
'
CREATE CLUSTERED INDEX IX_ActionLog[DD]_ActionTime
ON dbo.ActionLog[DD](ActionTime) ON FG[DD]
CREATE INDEX IX_ActionLog[DD]_CharName
ON dbo.ActionLog[DD](CharName) ON FG[DD]
CREATE INDEX IX_ActionLog[DD]_Text1
ON dbo.ActionLog[DD](Text1) ON FG[DD]
CREATE INDEX IX_ActionLog[DD]_Text2
ON dbo.ActionLog[DD](Text2) ON FG[DD]
'
-- 테이블 생성
SET @Cnt = 1
WHILE( @Cnt <= @DayMax)
BEGIN
-- 날짜 번호 구하기
SET @DD = CAST( @Cnt AS varchar(2) )
IF ( LEN(@DD) = 1 )
BEGIN
SET @DD = '0' + @DD
END
-- 앞날짜 뒷날짜 구하기
SET @Date1 = CAST(@YYYY + '-' + @MM + '-' + @DD AS datetime)
SET @Date2 = DATEADD(dd, 1, @Date1)
-- 각 part 별 날짜데이터 구하기
-- 앞날짜
SET @Tempyyyy1 = DATEPART(yyyy, @Date1)
SET @Tempmm1 = DATEPART(mm, @Date1)
IF( LEN ( @Tempmm1 ) = 1 )
BEGIN
SET @Tempmm1 = '0' + @Tempmm1
END
SET @Tempdd1 = DATEPART(dd, @Date1)
IF( LEN ( @Tempdd1 ) = 1 )
BEGIN
SET @Tempdd1 = '0' + @Tempdd1
END
SET @Date1c = @Tempyyyy1 + '-' + @Tempmm1 + '-' + @Tempdd1 + ' 00:00:00'
-- 뒷날짜
SET @Tempyyyy2 = DATEPART(yyyy, @Date2)
SET @Tempmm2 = DATEPART(mm, @Date2)
IF( LEN ( @Tempmm2 ) = 1 )
BEGIN
SET @Tempmm2 = '0' + @Tempmm2
END
SET @Tempdd2 = DATEPART(dd, @Date2)
IF( LEN ( @Tempdd2 ) = 1 )
BEGIN
SET @Tempdd2 = '0' + @Tempdd2
END
SET @Date2c = @Tempyyyy2 + '-' + @Tempmm2 + '-' + @Tempdd2 + ' 00:00:00'
SET @Sql1 = @SqlTbl
SET @Sql1 = REPLACE(@Sql1, '[DD]', @DD)
SET @Sql1 = REPLACE(@Sql1, '[Date1]', @Date1c)
SET @Sql1 = REPLACE(@Sql1, '[Date2]', @Date2c)
EXEC(@Sql3 + @Sql1)
-- SELECT (@Sql3 + @Sql1)
SET @Cnt = @Cnt + 1
END
-- 인덱스 생성
SET @Cnt = 1
WHILE( @Cnt <= @DayMax)
BEGIN
SET @DD = CAST( @Cnt AS varchar(2) )
IF ( LEN(@DD) = 1 )
BEGIN
SET @DD = '0' + @DD
END
SET @Sql2 = @SqlIdx
SET @Sql2 = REPLACE(@Sql2, '[DD]', @DD)
EXEC(@Sql3 + @Sql2)
-- SELECT (@Sql3 + @Sql2)
SET @Cnt = @Cnt + 1
END
GO
/****** Object: StoredProcedure [dbo].[uxp_CreateTable2] Script Date: 02/12/2011 17:12:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object: Stored Procedure dbo.uxp_CreateTable2 Script Date: 2008-6-7 18:32:40 ******/
/****** Object: Stored Procedure dbo.uxp_CreateTable2 Script Date: 2008/3/15 下午 05:16:53 ******/
CREATE Proc [dbo].[uxp_CreateTable2]
/*
Created by [Only registered and activated users can see links. Click Here To Register...], 2004-09-14
게임로그 DB 일별로 테이블 만들어주는 쿼리
*/
@DBName varchar(50) = 'PS_GameLog',
@DBDate datetime
AS
-- 필수 내장 변수
DECLARE @SqlTbl varchar(8000)
DECLARE @SqlIdx varchar(8000)
DECLARE @Sql1 varchar(4000)
DECLARE @Sql2 varchar(4000)
DECLARE @Sql3 varchar(100)
DECLARE @StDate datetime
DECLARE @EnDate datetime
DECLARE @Cnt int
DECLARE @DayMax int
DECLARE @YYYY varchar(4)
DECLARE @MM varchar(2)
DECLARE @DD varchar(2)
DECLARE @Date1 datetime
DECLARE @Date2 datetime
DECLARE @Tempyyyy1 varchar(4)
DECLARE @Tempyyyy2 varchar(4)
DECLARE @Tempmm1 varchar(2)
DECLARE @Tempmm2 varchar(2)
DECLARE @Tempdd1 varchar(2)
DECLARE @Tempdd2 varchar(2)
DECLARE @Date1c varchar(30)
DECLARE @Date2c varchar(30)
-- 날짜 셋팅
SET @YYYY = CAST ( DATEPART(yyyy, @DBDate) AS varchar(4) )
SET @MM = CAST ( DATEPART(mm, @DBDate) AS varchar(2) )
IF( LEN (@MM) = 1 )
BEGIN
SET @MM = '0' + @MM
END
-- 마지막날 구하기
SET @StDate = CAST(@YYYY + '-' + @MM + '-01' AS datetime)
SET @EnDate = DATEADD ( mm , 1, @StDate )
SET @EnDate = DATEADD ( dd , -1, @EnDate )
SET @DayMax = DATEPART(dd, @EnDate)
-- DBName 다시 설정 = DBName_YYYYMM
SET @DBName = @DBName + '_' + @YYYY + @MM
-- DB 사용 스크립트
SET @Sql3 = 'USE ' + @DBName
-- 테이블 생성 스크립트 선언
SET @SqlTbl = '
CREATE TABLE dbo.ActionLog[DD] (
RowID bigint IDENTITY (1, 1) NOT NULL ,
UserID varchar (18) NOT NULL ,
UserUID int NOT NULL ,
CharID int NOT NULL ,
CharName varchar (50) NOT NULL ,
CharLevel tinyint NOT NULL ,
CharExp int NOT NULL ,
MapID smallint NOT NULL ,
PosX real NOT NULL ,
PosY real NOT NULL ,
PosZ real NOT NULL ,
ActionTime datetime NOT NULL ,
ActionType tinyint NOT NULL ,
Value1 bigint NULL ,
Value2 int NULL ,
Value3 int NULL ,
Value4 bigint NULL ,
Value5 int NULL ,
Value6 int NULL ,
Value7 int NULL ,
Value8 int NULL ,
Value9 int NULL ,
Value10 int NULL ,
Text1 varchar (100) NULL ,
Text2 varchar (100) NULL ,
Text3 varchar (100) NULL ,
Text4 varchar (100) NULL ,
CONSTRAINT PK_ActionLog[DD]_RowID PRIMARY KEY NONCLUSTERED
(
RowID
) ON FG[DD] ,
CONSTRAINT CK_ActionLog[DD]_ActionTime CHECK (DATEPART(dd,ActionTime) = [CNT] )
) ON FG[DD]
'
-- 인덱스 생성 스크립트 선언
SET @SqlIdx =
'
CREATE CLUSTERED INDEX IX_ActionLog[DD]_ActionTime
ON dbo.ActionLog[DD](ActionTime) ON FG[DD]
CREATE INDEX IX_ActionLog[DD]_CharName
ON dbo.ActionLog[DD](CharName) ON FG[DD]
CREATE INDEX IX_ActionLog[DD]_Text1
ON dbo.ActionLog[DD](Text1) ON FG[DD]
CREATE INDEX IX_ActionLog[DD]_Text2
ON dbo.ActionLog[DD](Text2) ON FG[DD]
'
-- 테이블 생성
SET @Cnt = 1
WHILE( @Cnt <= @DayMax)
BEGIN
-- 날짜 번호 구하기
SET @DD = CAST( @Cnt AS varchar(2) )
IF ( LEN(@DD) = 1 )
BEGIN
SET @DD = '0' + @DD
END
-- 앞날짜 뒷날짜 구하기
SET @Date1 = CAST(@YYYY + '-' + @MM + '-' + @DD AS datetime)
SET @Date2 = DATEADD(dd, 1, @Date1)
-- 각 part 별 날짜데이터 구하기
-- 앞날짜
SET @Tempyyyy1 = DATEPART(yyyy, @Date1)
SET @Tempmm1 = DATEPART(mm, @Date1)
IF( LEN ( @Tempmm1 ) = 1 )
BEGIN
SET @Tempmm1 = '0' + @Tempmm1
END
SET @Tempdd1 = DATEPART(dd, @Date1)
IF( LEN ( @Tempdd1 ) = 1 )
BEGIN
SET @Tempdd1 = '0' + @Tempdd1
END
SET @Date1c = @Tempyyyy1 + '-' + @Tempmm1 + '-' + @Tempdd1 + ' 00:00:00'
-- 뒷날짜
SET @Tempyyyy2 = DATEPART(yyyy, @Date2)
SET @Tempmm2 = DATEPART(mm, @Date2)
IF( LEN ( @Tempmm2 ) = 1 )
BEGIN
SET @Tempmm2 = '0' + @Tempmm2
END
SET @Tempdd2 = DATEPART(dd, @Date2)
IF( LEN ( @Tempdd2 ) = 1 )
BEGIN
SET @Tempdd2 = '0' + @Tempdd2
END
SET @Date2c = @Tempyyyy2 + '-' + @Tempmm2 + '-' + @Tempdd2 + ' 00:00:00'
SET @Sql1 = @SqlTbl
SET @Sql1 = REPLACE(@Sql1, '[CNT]', CAST ( @Cnt AS varchar(2) ) )
SET @Sql1 = REPLACE(@Sql1, '[DD]', @DD)
SET @Sql1 = REPLACE(@Sql1, '[Date1]', @Date1c)
SET @Sql1 = REPLACE(@Sql1, '[Date2]', @Date2c)
EXEC(@Sql3 + @Sql1)
-- SELECT (@Sql3 + @Sql1)
SET @Cnt = @Cnt + 1
END
-- 인덱스 생성
SET @Cnt = 1
WHILE( @Cnt <= @DayMax)
BEGIN
SET @DD = CAST( @Cnt AS varchar(2) )
IF ( LEN(@DD) = 1 )
BEGIN
SET @DD = '0' + @DD
END
SET @Sql2 = @SqlIdx
SET @Sql2 = REPLACE(@Sql2, '[DD]', @DD)
EXEC(@Sql3 + @Sql2)
-- SELECT (@Sql3 + @Sql2)
SET @Cnt = @Cnt + 1
END
GO
/****** Object: StoredProcedure [dbo].[uxp_CreateView] Script Date: 02/12/2011 17:12:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object: Stored Procedure dbo.uxp_CreateView Script Date: 2008-6-7 18:32:40 ******/
/****** Object: Stored Procedure dbo.uxp_CreateView Script Date: 2008/3/15 下午 05:16:53 ******/
CREATE Proc [dbo].[uxp_CreateView]
/*
Created by [Only registered and activated users can see links. Click Here To Register...], 2004-10-14
게임로그 DB 통채로 묶어서 View로 만들어주는 프로시저
이 프로시저의 결과는 PS_GameLog에 생긴다 -> 현재 T-SQL로 방법 없음 T_T
*/
@DBName varchar(30) = 'PS_GameLog',
@DBDate datetime
AS
-- 필수 내장 변수
DECLARE @SqlDef1 varchar(2000)
DECLARE @SqlDef2 varchar(1000)
DECLARE @SqlDef3 varchar(1000)
DECLARE @SqlDef4 varchar(200)
DECLARE @Sql1 varchar(8000)
DECLARE @Sql2 varchar(8000)
DECLARE @YYYY varchar(4)
DECLARE @MM varchar(2)
DECLARE @DD varchar(2)
DECLARE @StDate datetime
DECLARE @EnDate datetime
DECLARE @Cnt int
DECLARE @DayMax int
DECLARE @Date1 datetime
DECLARE @Date2 datetime
DECLARE @Date1c varchar(30)
DECLARE @Date2c varchar(30)
-- 날짜 셋팅
SET @YYYY = CAST ( DATEPART(yyyy, @DBDate) AS varchar(4) )
SET @MM = CAST ( DATEPART(mm, @DBDate) AS varchar(2) )
IF( LEN (@MM) = 1 )
BEGIN
SET @MM = '0' + @MM
END
-- 마지막날 구하기
SET @StDate = CAST(@YYYY + '-' + @MM + '-01' AS datetime)
SET @EnDate = DATEADD ( mm , 1, @StDate )
SET @EnDate = DATEADD ( dd , -1, @EnDate )
SET @DayMax = DATEPART(dd, @EnDate)
-- DBName 다시 설정 = DBName_YYYYMM
SET @DBName = @DBName + '_' + @YYYY + @MM
-- 시작 부분 설정
SET @SqlDef1 = '
CREATE View dbo.View_ActionLog_' + @YYYY + @MM + '
AS
'
-- SELECT 부분
SET @SqlDef2 = '
SELECT *
FROM ' + @DBName + '.dbo.ActionLog[DD]
'
-- UNION ALL
SET @SqlDef3 = '
UNION ALL
'
-- WITH CHECK OPTION
SET @SqlDef4 = '
WITH CHECK OPTION
'
-- 필요 변수 초기화
SET @Cnt = 1
SET @Sql1 = ''
SET @Sql2 = ''
WHILE( @Cnt <= @DayMax)
BEGIN
-- 날짜 번호 구하기
SET @DD = CAST( @Cnt AS varchar(2) )
IF ( LEN(@DD) = 1 )
BEGIN
SET @DD = '0' + @DD
END
SET @Sql2 = @SqlDef2
SET @Sql2 = REPLACE(@Sql2, '[DD]', @DD)
-- 마지막날만 빼고 union all
IF(@Cnt < @DayMax)
BEGIN
SET @Sql2 = @Sql2 + @SqlDef3
END
SET @Sql1 = @Sql1 + @Sql2
SET @Cnt = @Cnt + 1
END
EXEC (@SqlDef1 + @Sql1)
--SELECT @SqlDef1 + @Sql1
GO
/****** Object: StoredProcedure [dbo].[usp_Insert_Action_Log_E] Script Date: 02/12/2011 17:12:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object: Stored Procedure dbo.usp_Insert_Action_Log_E Script Date: 2008-6-7 18:32:40 ******/
/****** Object: Stored Procedure dbo.usp_Insert_Action_Log_E Script Date: 2008/3/15 下午 05:16:53 ******/
CREATE Proc [dbo].[usp_Insert_Action_Log_E]
/*
Created by [Only registered and activated users can see links. Click Here To Register...], 2004-08-17
Modified by [Only registered and activated users can see links. Click Here To Register...], 2004-08-19
게임내 행동 로그 남기기 */
/*
*/
@UserID varchar(18),
@UserUID int,
@CharID int,
@CharName varchar(50),
@CharLevel tinyint,
@CharExp int,
@MapID smallint,
@PosX real,
@PosY real,
@PosZ real,
@ActionTime datetime,
@ActionType tinyint,
@Value1 bigint = null,
@Value2 int = null,
@Value3 int = null,
@Value4 bigint = null,
@Value5 int = null,
@Value6 int = null,
@Value7 int = null,
@Value8 int = null,
@Value9 int = null,
@Value10 int = null,
@Text1 varchar(100) = '',
@Text2 varchar(100) = '',
@Text3 varchar(100) = '',
@Text4 varchar(100) = '',
@Sql nvarchar(4000) = '',
@yyyy varchar(4) = '',
@mm varchar(2) = '',
@dd varchar(2) = '',
@Bucket smallint = -1
AS
SET @yyyy = datepart(yyyy, @ActionTime)
SET @mm = datepart(mm, @ActionTime)
SET @dd = datepart(dd, @ActionTime)
IF(LEN(@mm) = 1)
BEGIN
SET @mm = '0' + @mm
END
IF(LEN(@dd) = 1)
BEGIN
SET @dd = '0' + @dd
END
SET @Sql = N'
INSERT INTO PS_GameLog.dbo.ActionLog
(UserID, UserUID, CharID, CharName, CharLevel, CharExp, MapID, PosX, PosY, PosZ, ActionTime, ActionType,
Value1, Value2, Value3, Value4, Value5, Value6, Value7, Value8, Value9, Value10, Text1, Text2, Text3, Text4)
VALUES(@UserID, @UserUID, @CharID, @CharName, @CharLevel, @CharExp, @MapID, @PosX, @PosY, @PosZ, @ActionTime, @ActionType,
@Value1, @Value2, @Value3, @Value4, @Value5, @Value6, @Value7, @Value8, @Value9, @Value10, @Text1, @Text2, @Text3, @Text4)'
EXEC sp_executesql @Sql,
N'@UserID varchar(18), @UserUID int, @CharID int, @CharName varchar(50),
@CharLevel tinyint, @CharExp int, @MapID smallint, @PosX real, @PosY real, @PosZ real, @ActionTime datetime, @ActionType tinyint,
@Value1 bigint, @Value2 int, @Value3 int, @Value4 bigint, @Value5 int, @Value6 int, @Value7 int, @Value8 int,
@Value9 int, @Value10 int, @Text1 varchar(100), @Text2 varchar(100), @Text3 varchar(100), @Text4 varchar(100)',
@UserID, @UserUID, @CharID, @CharName, @CharLevel, @CharExp, @MapID, @PosX, @PosY, @PosZ, @ActionTime, @ActionType,
@Value1, @Value2, @Value3, @Value4, @Value5, @Value6, @Value7, @Value8, @Value9, @Value10, @Text1, @Text2, @Text3, @Text4
--Add Items to TradedItems Table
If(@ActionType = 116 or @ActionType = 164)
BEGIN
SET @Sql = N'
INSERT INTO PS_GameLog.dbo.TradedItems
(UserID, UserUID, CharID, CharName, CharLevel, CharExp, MapID, PosX, PosY, PosZ, ActionTime, ActionType,
Value1, Value2, Value3, Value4, Value5, Value6, Value7, Value8, Value9, Value10, Text1, Text2, Text3, Text4)
VALUES(@UserID, @UserUID, @CharID, @CharName, @CharLevel, @CharExp, @MapID, @PosX, @PosY, @PosZ, @ActionTime, @ActionType,
@Value1, @Value2, @Value3, @Value4, @Value5, @Value6, @Value7, @Value8, @Value9, @Value10, @Text1, @Text2, @Text3, @Text4)'
EXEC sp_executesql @Sql,
N'@UserID varchar(18), @UserUID int, @CharID int, @CharName varchar(50),
@CharLevel tinyint, @CharExp int, @MapID smallint, @PosX real, @PosY real, @PosZ real, @ActionTime datetime, @ActionType tinyint,
@Value1 bigint, @Value2 int, @Value3 int, @Value4 bigint, @Value5 int, @Value6 int, @Value7 int, @Value8 int,
@Value9 int, @Value10 int, @Text1 varchar(100), @Text2 varchar(100), @Text3 varchar(100), @Text4 varchar(100)',
@UserID, @UserUID, @CharID, @CharName, @CharLevel, @CharExp, @MapID, @PosX, @PosY, @PosZ, @ActionTime, @ActionType,
@Value1, @Value2, @Value3, @Value4, @Value5, @Value6, @Value7, @Value8, @Value9, @Value10, @Text1, @Text2, @Text3, @Text4
END
GO
/****** Object: StoredProcedure [dbo].[usp_Insert_ItemMake_Log_E] Script Date: 02/12/2011 17:12:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object: Stored Procedure dbo.usp_Insert_ItemMake_Log_E Script Date: 2008-6-7 18:32:40 ******/
/****** Object: Stored Procedure dbo.usp_Insert_ItemMake_Log_E Script Date: 2008/3/15 下午 05:16:53 ******/
CREATE Proc [dbo].[usp_Insert_ItemMake_Log_E]
/* Created by [Only registered and activated users can see links. Click Here To Register...], 2004-08-13
아이템 생성 로그 남기기 */
/*
행번호 테이블의 단순 행번호, 1씩 증가한다(int)
날짜/시간 몹이 드롭하여 생성된 시간을 기록한다(datetime)
생성타입 D : 몬스터로 부터 드롭 / T : 다른 PC에게서 얻음 / B : NPC 에게서 구매 / Q : 퀘스트로 얻음 / C : 크래프트로 생성 / S : 시작시 제공
몹ID 몹의 ID를 기록한다(smallint)
당시맵위치 몹을 잡을때 그 맵 위치를 기록한다(x,y,z 각각 real)
최초소유가능자 ID 몹을 잡았을때 최초로 소유가능한 캐릭터의 ID를 기록한다(int)
상점ID 상점 구입 아이템일 경우 상점 ID를 기록한다(smallint)
아이템ID 아이템 ID를 기록한다(int)
아이템UniqueID 아이템 생성당시 Unique ID를 기록한다(bigint)
*/
@MakeTime datetime,
@MakeType char(1) = '',
@MobID smallint = null,
@MapID smallint,
@PosX real = null,
@PosY real = null,
@PosZ real = null,
@FOID int = null,
@ShopID int = null,
@ItemID int = null,
@ItemUID bigint = null,
@ItemName varchar(30) = ''
AS
IF(@ItemName = '')
BEGIN
SET @ItemName = (SELECT ItemName FROM PS_GameDefs.dbo.Items WHERE ItemID = @ItemID)
END
INSERT INTO PS_ItemCreateLog.dbo.ItemMakeLog(MakeTime, MakeType, MapID, MobID, PosX, PosY, PosZ,
FirstOwnerableID, ShopID, ItemID, ItemUID, ItemName)
VALUES(@MakeTime, @MakeType, @MapID, @MobID, @PosX, @PosY, @PosZ, @FOID, @ShopID, @ItemID, @ItemUID, @ItemName)
GO
/****** Object: StoredProcedure [dbo].[usp_Insert_Queue_E] Script Date: 02/12/2011 17:12:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object: Stored Procedure dbo.usp_Insert_Queue_E Script Date: 2008-6-7 18:32:40 ******/
/****** Object: Stored Procedure dbo.usp_Insert_Queue_E Script Date: 2008/3/15 下午 05:16:53 ******/
CREATE Proc [dbo].[usp_Insert_Queue_E]
/*
Created by [Only registered and activated users can see links. Click Here To Register...], 2004-08-28
게임내 유저 질문 큐에서 내용 저장
*/
/* Type 정의..
1 : 질문받음(대기)
2 : 처리
3 : 삭제
4 : 보류
*/
@QueueID int,
@CharID int,
@CharName varchar(50),
@Question varchar(2000),
@OwnerAdminID varchar(30),
@Type tinyint = 1,
@QuestionDate datetime
AS
INSERT INTO QuestionQueueList(QueueID, CharID, CharName, Question, Type, OwnerAdminID, QuestionDate)
VALUES(@QueueID, @CharID, @CharName, @Question, @Type, @OwnerAdminID, @QuestionDate)
GO
/****** Object: StoredProcedure [dbo].[usp_Read_Action_Log_R] Script Date: 02/12/2011 17:12:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/****** Object: Stored Procedure dbo.usp_Read_Action_Log_R Script Date: 2008-6-7 18:32:40 ******/
/****** Object: Stored Procedure dbo.usp_Read_Action_Log_R Script Date: 2008/3/15 下午 05:16:53 ******/
CREATE Proc [dbo].[usp_Read_Action_Log_R]
/*
Created by [Only registered and activated users can see links. Click Here To Register...], 2004-08-25
게임내 행동 로그 읽기
검색순서
계정 -> 캐릭터 -> 타입 -> 텍스트1 -> 텍스트2 -> 날짜
계정명 및 캐릭터는 LIKE 검색안됩니다.
타입은 중복 허용 가능합니다.(111,112,113)
텍스트1, 2는 LIKE '텍스트%' 로 검색됨
어떤 검색조건도 없으면, 결과가 나오지 않습니다..
날짜 필수
Modified by [Only registered and activated users can see links. Click Here To Register...], 2004-09-15
ActionLog -> ActionLog[DD] 의 인라인 분산뷰로 전환
필요한 날짜 자동 감지하여 인라인 뷰 생성
*/
@UserID varchar(18) = '',
@CharName varchar(50) = '',
@ActionType varchar(50) = '', -- 대충 10개정도까지 중복가능. 그 이상은 무리..
@Text1 varchar(50) = '',
@Text2 varchar(50) = '',
@StDate datetime, -- 검색시작일, 종료일 필수
@EnDate datetime,
@Cond varchar(1000) = '',
@yyyy varchar(4) = '',
@mm varchar(2) = '',
@dd varchar(2) = '',
@stdd int = 0,
@endd int = 0,
@SqlDef varchar(8000) = '',
@SqlTop varchar(8000) = '',
@SqlView varchar(8000) = '',
@Sql1 varchar(8000) = '',
@Sql2 varchar(8000) = '',
@SqlAll varchar(8000) = '',
@Cnt int = 0,
@DBCnt int = 0,
@TableSCnt int = 0,
@TableECnt int = 0
AS
-- 시작일로부터 yyyymm 구하기
SET @yyyy = DATEPART(yyyy, @StDate)
SET @mm = DATEPART(mm, @StDate)
IF( LEN(@mm) = 1)
BEGIN
SET @mm = '0' + @mm
END
-- DB가 있는지 확인
-- SQL 문장
-- 최상위 SELECT 부분
SET @SqlTop = 'SELECT UserID, UserUID, CharID, CharName, CharLevel, CharExp,
MapID, PosX, PosY, PosZ, ActionTime, ActionType,
Value1, Value2, Value3, Value4, Value5, Value6, Value7, Value8, Value9, Value10,
Text1, Text2, Text3, Text4 FROM (
'
SET @SqlDef =
'
SELECT *
FROM PS_GameLog_' + @yyyy + @mm + '.dbo.ActionLog[DD]
'
SET @stdd = DATEPART(dd, @StDate)
SET @endd = DATEPART(dd, @EnDate)
SET @Cnt = @stdd
WHILE(@Cnt <= @endd)
BEGIN
SET @Sql1 = @SqlDef
SET @DD = CAST ( @Cnt as varchar (2) )
IF( LEN (@DD) = 1 )
BEGIN
SET @DD = '0' + @DD
END
SET @Sql1 = REPLACE(@Sql1, '[DD]', @DD)
SET @SqlView = @SqlView + @Sql1
IF(@Cnt < @endd)
BEGIN
SET @SqlView = @SqlView + ' UNION ALL '
END
SET @Cnt = @Cnt + 1
END
SET @SqlAll = @SqlTop + @SqlView + ' ) AS T '
-- 검색조건..
-- 우선 돌려보고, 검색 순서에 따라서 순서 조정 가능
IF(@UserID <> '')
BEGIN
IF(@Cond = '')
BEGIN
SET @Cond = ' WHERE ( UserID = ''' + @UserID + ''' ) '
END
ELSE
BEGIN
SET @Cond = @Cond + ' AND ( UserID = ''' + @UserID + ''' ) '
END
END
IF(@CharName <> '')
BEGIN
IF(@Cond = '')
BEGIN
SET @Cond = ' WHERE ( CharName = ''' + @CharName + ''' ) '
END
ELSE
BEGIN
SET @Cond = @Cond + ' AND ( CharName = ''' + @CharName + ''' ) '
END
END
IF(@ActionType <> '')
BEGIN
IF(@Cond = '')
BEGIN
SET @Cond = ' WHERE ( ActionType IN (' + @ActionType + '))'
END
ELSE
BEGIN
SET @Cond = @Cond + ' AND ( ActionType IN (' + @ActionType + '))'
END
END
IF(@Text1 <> '')
BEGIN
IF(@Cond = '')
BEGIN
SET @Cond = ' WHERE ( Text1 = ''' + @Text1 + ''' ) '
END
ELSE
BEGIN
SET @Cond = @Cond + ' AND ( Text1 = ''' + @Text1 + ''' ) '
END
END
IF(@Text2 <> '')
BEGIN
IF(@Cond = '')
BEGIN
SET @Cond = ' WHERE ( Text2 = ''' + @Text2 + ''' ) '
END
ELSE
BEGIN
SET @Cond = @Cond + ' AND ( Text2 = ''' + @Text2 + ''' ) '
END
END
IF( (@StDate IS NOT NULL) AND (@EnDate IS NOT NULL) )
BEGIN
IF(@Cond = '')
BEGIN
SET @Cond = ' WHERE ( ActionTime >= ''' + CONVERT(varchar(40), @StDate, 120) + ''' AND ActionTime <= ''' + CONVERT(varchar(40), @EnDate, 120) + ''' ) '
END
ELSE
BEGIN
SET @Cond = @Cond + ' AND ( ActionTime >= ''' + CONVERT(varchar(40), @StDate, 120) + ''' AND ActionTime <= ''' + CONVERT(varchar(40), @EnDate, 120) + ''' ) '
END
END
IF(@Cond <> '')
BEGIN
SET @SqlAll = @SqlAll + @Cond
EXEC(@SqlAll)
-- SELECT (@SqlAll)
END
ELSE
BEGIN
RETURN -1
END
GO
/****** Object: StoredProcedure [dbo].[usp_Read_ItemCreateLog_R] Script Date: 02/12/2011 17:12:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object: Stored Procedure dbo.usp_Read_ItemCreateLog_R Script Date: 2008-6-7 18:32:40 ******/
/****** Object: Stored Procedure dbo.usp_Read_ItemCreateLog_R Script Date: 2008/3/15 下午 05:16:53 ******/
CREATE Proc [dbo].[usp_Read_ItemCreateLog_R]
/*
Created by [Only registered and activated users can see links. Click Here To Register...], 2004-08-25
Modified by [Only registered and activated users can see links. Click Here To Register...], 2004-08-27
아이템 생성로그 읽어오기(검색)
하단 검색조건:
아이템ID, 아이템명, 조회시작-마감일,
생성타입(D : 몬스터로 부터 드롭 / T : 다른 PC에게서 얻음 / B : NPC 에게서 구매
Q : 퀘스트로 얻음 / C : 크래프트로 생성 / S : 캐릭 생성시 기본아이템 )
* LIKE 검색 지원X
*/
@ItemID varchar(70) = '',
--@ItemName varchar(30) = '',
@MakeType char(1) = '',
@StDate datetime = null,
@EnDate datetime = null,
@Cond varchar(300) = '',
@Sql varchar(1000) = ''
AS
SET @Sql = 'SELECT MakeTime, MakeType, MobID, MapID,
PosX, PosY, PosZ, FirstOwnerableID, ShopID, ItemID, ItemUID, ItemName
FROM PS_ItemCreateLog.dbo.ItemMakeLog '
/* 하단검색조건..
검색조건 순위 그냥 IF ~ END 통채로 긁어다가 바꿔도 됩니다~
*/
IF(@ItemID <> '')
BEGIN
IF(@Cond = '')
BEGIN
SET @Cond = ' WHERE ( ItemID IN (''' + @ItemID + ''') ) '
END
ELSE
BEGIN
SET @Cond = @Cond + ' AND ( ItemID IN (''' + @ItemID + ''') ) '
END
END
/*
IF(@ItemName <> '')
BEGIN
IF(@Cond = '')
BEGIN
SET @Cond = ' WHERE ( ItemName = ''' + @ItemName + ''' ) '
END
ELSE
BEGIN
SET @Cond = @Cond + ' AND ( ItemName = ''' + @ItemName + ''' ) '
END
END
*/
IF(@MakeType <> '')
BEGIN
IF(@Cond = '')
BEGIN
SET @Cond = ' WHERE ( MakeType = ''' + @MakeType + ''' ) '
END
ELSE
BEGIN
SET @Cond = @Cond + ' AND ( MakeType = ''' + @MakeType + ''' ) '
END
END
IF( (@StDate IS NOT NULL) AND (@EnDate IS NOT NULL) )
BEGIN
IF(@Cond = '')
BEGIN
SET @Cond = ' WHERE ( MakeTime >= ''' + CONVERT(varchar(40), @StDate, 120) + ''' AND MakeTime <= ''' + CONVERT(varchar(40), @EnDate, 120) + ''' ) '
END
ELSE
BEGIN
SET @Cond = @Cond + ' AND ( MakeTime >= ''' + CONVERT(varchar(40), @StDate, 120) + ''' AND MakeTime <= ''' + CONVERT(varchar(40), @EnDate, 120) + ''' ) '
END
END
IF(@Cond <> '')
BEGIN
SET @Sql = @Sql + @Cond
EXEC(@Sql)
END
ELSE
BEGIN
RETURN -1
END
GO
/****** Object: StoredProcedure [dbo].[usp_Update_Queue_E] Script Date: 02/12/2011 17:12:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object: Stored Procedure dbo.usp_Update_Queue_E Script Date: 2008-6-7 18:32:40 ******/
/****** Object: Stored Procedure dbo.usp_Update_Queue_E Script Date: 2008/3/15 下午 05:16:53 ******/
CREATE Proc [dbo].[usp_Update_Queue_E]
/*
Created by [Only registered and activated users can see links. Click Here To Register...], 2004-08-28
게임내 유저 질문 큐 답변처리/삭제/보류/담당자 토스 시 발생
*/
/* 어떤 형식으로던 처리가 되면 자동으로(Trigger 구현)
운영DB로 데이터가 넘어가게 됨
*/
/* Type 정의..
1 : 질문받음(대기)
2 : 처리
3 : 삭제
4 : 보류
*/
@QueueID int,
@Answer varchar(4000),
@OwnerAdminID varchar(30),
@Type tinyint,
@AnswerDate datetime
AS
UPDATE PS_GameLog.dbo.QuestionQueueList
SET Answer=@Answer, Type=@Type, OwnerAdminID=@OwnerAdminID, AnswerDate=@AnswerDate
WHERE QueueID = @QueueID
GO
/****** Object: StoredProcedure [dbo].[usp_Read_ActionTypeDefs_R] Script Date: 02/12/2011 17:12:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object: Stored Procedure dbo.usp_Read_ActionTypeDefs_R Script Date: 2008-6-7 18:32:40 ******/
/****** Object: Stored Procedure dbo.usp_Read_ActionTypeDefs_R Script Date: 2008/3/15 下午 05:16:53 ******/
CREATE Proc [dbo].[usp_Read_ActionTypeDefs_R]
AS
SELECT ActionTypeID, ActionTypeName, BindText, Value1_Desc, Value2_Desc, Value3_Desc, Value4_Desc, Value5_Desc, Value6_Desc, Value7_Desc, Value8_Desc, Value9_Desc, Value10_Desc, Text1_Desc, Text2_Desc, Text3_Desc, Text4_Desc
FROM ActionTypeDefs
ORDER BY ActionTypeID ASC
GO