Polub 0
Zforkuj 0

Ostatnio aktywny 1 month ago

LogDB_ Procedure

archgeus zrewidował ten Gist 2 years ago. Przejdź do rewizji

1 file changed, 0 insertions, 0 deletions

gistfile1.txt zmieniono nazwę na LogDB.sql

Zmieniono nazwę pliku bez modyfikacji zawartości

archgeus zrewidował ten Gist 2 years ago. Przejdź do rewizji

1 file changed, 337 insertions

gistfile1.txt(stworzono plik)

@@ -0,0 +1,337 @@
1 + USE [LogDB_2800]
2 + GO
3 + /****** Object: StoredProcedure [dbo].[spCreateBcpResult] Script Date: 7/19/2022 8:22:02 PM ******/
4 + SET ANSI_NULLS ON
5 + GO
6 + SET QUOTED_IDENTIFIER ON
7 + GO
8 + -- =============================================
9 + -- Author: <Author,,Name>
10 + -- Create date: <Create Date,,>
11 + -- Description: <Description,,>
12 + -- =============================================
13 + CREATE PROCEDURE [dbo].[spCreateBcpResult]
14 + -- Add the parameters for the stored procedure here
15 + @logFileName nvarchar(256),
16 + @movedFileName nvarchar(256),
17 + @bcpLines int
18 +
19 + AS
20 + BEGIN
21 + -- SET NOCOUNT ON added to prevent extra result sets from
22 + -- interfering with SELECT statements.
23 + SET NOCOUNT ON;
24 +
25 + insert into bcpResult( logFileName, movedFileName, bcpLines)
26 + values
27 + (@logFileName, @movedFileName, @bcpLines)
28 + END
29 + GO
30 + /****** Object: StoredProcedure [dbo].[spCreateBcpResult2] Script Date: 7/19/2022 8:22:02 PM ******/
31 + SET ANSI_NULLS ON
32 + GO
33 + SET QUOTED_IDENTIFIER ON
34 + GO
35 + CREATE PROCEDURE [dbo].[spCreateBcpResult2]
36 + -- Add the parameters for the stored procedure here
37 + @logFileName nvarchar(256),
38 + @movedFileName nvarchar(256),
39 + @bcpLines int,
40 + @execTime bigint
41 +
42 + AS
43 + BEGIN
44 + -- SET NOCOUNT ON added to prevent extra result sets from
45 + -- interfering with SELECT statements.
46 + SET NOCOUNT ON;
47 +
48 + if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME='bcpResult2')
49 + create table bcpResult2 (
50 + bcpTime timestamp,
51 + logFileName nvarchar(256),
52 + movedFileName nvarchar(256),
53 + bcpLines int,
54 + execTime bigint)
55 +
56 + insert into bcpResult2( logFileName, movedFileName, bcpLines, execTime)
57 + values
58 + (@logFileName, @movedFileName, @bcpLines, @execTime)
59 + END
60 + GO
61 + /****** Object: StoredProcedure [dbo].[spCreateLogTableChat] Script Date: 7/19/2022 8:22:02 PM ******/
62 + SET ANSI_NULLS ON
63 + GO
64 + SET QUOTED_IDENTIFIER ON
65 + GO
66 + CREATE PROCEDURE [dbo].[spCreateLogTableChat]
67 + @logTableName NVARCHAR(128)
68 + AS
69 + BEGIN
70 + SET NOCOUNT ON;
71 +
72 + DECLARE @sqlquery VARCHAR(1024);
73 +
74 + SET @sqlquery =
75 + 'if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME=''' + @logTableName + ''') \
76 + begin \
77 + create table ' + @logTableName + '( \
78 + timeStamp datetime,\
79 + planetId int,\
80 + serverType int,\
81 + logType int,\
82 + 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,\
83 + int64Val1 bigint, int64Val2 bigint, int64Val3 bigint, int64Val4 bigint,\
84 + itemDbId bigint,\
85 + contId int, channelId int, posX int, posY int, posZ int, \
86 + stringVal1 nvarchar(128), stringVal2 nvarchar(128), stringVal3 nvarchar(128), stringVal4 nvarchar(128) , chat nvarchar(516) )
87 +
88 + CREATE CLUSTERED INDEX IX_' + @logTableName + ' ON dbo.' + @logTableName + '( \
89 + logType, intVal1 ) \
90 +
91 + end ';
92 +
93 + EXEC (@sqlquery);
94 +
95 +
96 +
97 + END
98 + GO
99 + /****** Object: StoredProcedure [dbo].[spCreateLogTableChat_Old] Script Date: 7/19/2022 8:22:02 PM ******/
100 + SET ANSI_NULLS ON
101 + GO
102 + SET QUOTED_IDENTIFIER ON
103 + GO
104 + CREATE PROCEDURE [dbo].[spCreateLogTableChat_Old]
105 + @logTableName NVARCHAR(128)
106 + AS
107 + BEGIN
108 + SET NOCOUNT ON;
109 +
110 + DECLARE @sqlquery VARCHAR(1024);
111 +
112 + SET @sqlquery =
113 + 'if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME=''' + @logTableName + ''') \
114 + begin \
115 + create table ' + @logTableName + '( \
116 + timeStamp datetime,\
117 + planetId int,\
118 + serverType int,\
119 + logType int,\
120 + intVal1 int, intVal2 int, intVal3 int, intVal4 int, intVal5 int, intVal6 int, intVal7 int, intVal8 int, intVal9 int, intVal10 int,\
121 + doubleVal1 float, doubleVal2 float, doubleVal3 float, doubleVal4 float,\
122 + int64Val1 bigint, int64Val2 bigint,\
123 + itemDbId bigint,\
124 + contId int, channelId int, posX int, posY int, posZ int, \
125 + stringVal1 nvarchar(128), stringVal2 nvarchar(128), stringVal3 nvarchar(128), stringVal4 nvarchar(128) , chat nvarchar(516) )
126 +
127 + CREATE CLUSTERED INDEX IX_' + @logTableName + ' ON dbo.' + @logTableName + '( \
128 + logType, intVal1 ) \
129 +
130 + end ';
131 +
132 + EXEC (@sqlquery);
133 +
134 +
135 +
136 + END
137 + GO
138 + /****** Object: StoredProcedure [dbo].[spCreateLogTableEx] Script Date: 7/19/2022 8:22:02 PM ******/
139 + SET ANSI_NULLS ON
140 + GO
141 + SET QUOTED_IDENTIFIER ON
142 + GO
143 + /* 로그 테이블 만들기 프로시져 생성 */
144 + CREATE PROCEDURE [dbo].[spCreateLogTableEx]
145 + @logTableName NVARCHAR(128)
146 + AS
147 + BEGIN
148 + SET NOCOUNT ON;
149 +
150 + DECLARE @sqlquery VARCHAR(2048);
151 +
152 + SET @sqlquery =
153 + 'if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME=''' + @logTableName + ''') \
154 + begin \
155 +
156 + create table ' + @logTableName + '( \
157 + timeStamp datetime,\
158 + planetId int,\
159 + serverType int,\
160 + logType int,\
161 + 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,\
162 + int64Val1 bigint, int64Val2 bigint, int64Val3 bigint, int64Val4 bigint,\
163 + itemDbId bigint,\
164 + contId int, channelId int, posX int, posY int, posZ int, \
165 + stringVal1 nvarchar(128), stringVal2 nvarchar(128), stringVal3 nvarchar(128), stringVal4 nvarchar(128)) \
166 +
167 + CREATE CLUSTERED INDEX IX_' + @logTableName + ' ON dbo.' + @logTableName + ' (timeStamp, stringVal1, logType) \
168 + CREATE NONCLUSTERED INDEX IX2_' + @logTableName + ' ON dbo.' + @logTableName + ' (itemDbId, timeStamp, logType) \
169 +
170 + end ';
171 +
172 + /*
173 + CREATE CLUSTERED INDEX IX_' + @logTableName + ' ON dbo.' + @logTableName + ' (logType, intVal1, intVal2, intVal3 ) \
174 + CREATE NONCLUSTERED INDEX IX2_' + @logTableName + ' ON dbo.' + @logTableName + ' (stringVal1) \
175 + CREATE NONCLUSTERED INDEX IX3_' + @logTableName + ' ON dbo.' + @logTableName + ' (itemDbId) where (itemDbId <> 0) \
176 + */
177 +
178 + EXEC (@sqlquery);
179 + END
180 + GO
181 + /****** Object: StoredProcedure [dbo].[spCreateLogTableEx_Old] Script Date: 7/19/2022 8:22:02 PM ******/
182 + SET ANSI_NULLS ON
183 + GO
184 + SET QUOTED_IDENTIFIER ON
185 + GO
186 + CREATE PROCEDURE [dbo].[spCreateLogTableEx_Old]
187 + @logTableName NVARCHAR(128)
188 + AS
189 + BEGIN
190 + SET NOCOUNT ON;
191 +
192 + DECLARE @sqlquery VARCHAR(1024);
193 +
194 + SET @sqlquery =
195 + 'if not exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME=''' + @logTableName + ''') \
196 + begin \
197 + create table ' + @logTableName + '( \
198 + timeStamp datetime,\
199 + planetId int,\
200 + serverType int,\
201 + logType int,\
202 + intVal1 int, intVal2 int, intVal3 int, intVal4 int, intVal5 int, intVal6 int, intVal7 int, intVal8 int, intVal9 int, intVal10 int,\
203 + doubleVal1 float, doubleVal2 float, doubleVal3 float, doubleVal4 float,\
204 + int64Val1 bigint, int64Val2 bigint,\
205 + itemDbId bigint,\
206 + contId int, channelId int, posX int, posY int, posZ int, \
207 + stringVal1 nvarchar(128), stringVal2 nvarchar(128), stringVal3 nvarchar(128), stringVal4 nvarchar(128)) \
208 +
209 + CREATE CLUSTERED INDEX IX_' + @logTableName + ' ON dbo.' + @logTableName + '( \
210 + logType, intVal1, intVal2, intVal3 )
211 +
212 + end ';
213 +
214 +
215 + EXEC (@sqlquery);
216 +
217 +
218 + END
219 + GO
220 + /****** Object: StoredProcedure [dbo].[spDeleteOldLogTable] Script Date: 7/19/2022 8:22:02 PM ******/
221 + SET ANSI_NULLS ON
222 + GO
223 + SET QUOTED_IDENTIFIER ON
224 + GO
225 + -- =============================================
226 + -- Author: <Author,,Name>
227 + -- Create date: <Create Date,,>
228 + -- Description: <Description,,>
229 + -- =============================================
230 + CREATE PROCEDURE [dbo].[spDeleteOldLogTable]
231 + @daysBefore int
232 +
233 + AS
234 + BEGIN
235 + -- SET NOCOUNT ON added to prevent extra result sets from
236 + -- interfering with SELECT statements.
237 + SET NOCOUNT ON;
238 +
239 +
240 +
241 + DECLARE tables_cursor CURSOR
242 + FOR
243 + select name from sys.objects (nolock)
244 + WHERE type='U' and create_date < dateadd(day, @daysBefore, GETUTCDATE())
245 + and ( name like 'GAMELOG%' or name like 'CHATLOG%' or name like 'AUDITLOG%' )
246 +
247 + OPEN tables_cursor
248 + DECLARE @tablename sysname
249 + FETCH NEXT FROM tables_cursor INTO @tablename
250 + WHILE (@@FETCH_STATUS <> -1)
251 + BEGIN
252 + EXEC ( 'drop table ' + @tablename)
253 + -- EXEC ( 'select ''' + @tablename + ''' ')
254 +
255 + FETCH NEXT FROM tables_cursor INTO @tablename
256 + END
257 + DEALLOCATE tables_cursor
258 +
259 +
260 +
261 +
262 + END
263 + GO
264 + /****** Object: StoredProcedure [dbo].[spGetAssertLogs] Script Date: 7/19/2022 8:22:02 PM ******/
265 + SET ANSI_NULLS ON
266 + GO
267 + SET QUOTED_IDENTIFIER ON
268 + GO
269 + CREATE procedure [dbo].[spGetAssertLogs]
270 + as
271 + begin
272 + set nocount on;
273 +
274 + declare @lastTime datetime;
275 + set @lastTime = (select fetchtime from AssertLogLastFetchedInfo);
276 +
277 + declare @curTime datetime;
278 + set @curTime = GETUTCDATE();
279 +
280 + delete from AssertLogLastFetchedInfo;
281 +
282 + insert into AssertLogLastFetchedInfo (fetchtime) values (@curTime);
283 +
284 + select timeStamp, planetId, serverType, stringVal1, stringVal2
285 + from AssertLog
286 + where timeStamp >= @lastTime and timeStamp < @curTime;
287 + end
288 + GO
289 + /****** Object: StoredProcedure [dbo].[spLogDirect] Script Date: 7/19/2022 8:22:02 PM ******/
290 + SET ANSI_NULLS ON
291 + GO
292 + SET QUOTED_IDENTIFIER ON
293 + GO
294 + CREATE procedure [dbo].[spLogDirect]
295 + @timeStamp datetime,
296 + @planetId int,
297 + @serverType int,
298 + @logType int,
299 + @intVal1 int,
300 + @intVal2 int,
301 + @intVal3 int,
302 + @intVal4 int,
303 + @intVal5 int,
304 + @intVal6 int,
305 + @intVal7 int,
306 + @intVal8 int,
307 + @intVal9 int,
308 + @intVal10 int,
309 + @intVal11 int,
310 + @intVal12 int,
311 + @intVal13 int,
312 + @intVal14 int,
313 + @intVal15 int,
314 + @int64Val1 bigint,
315 + @int64Val2 bigint,
316 + @int64Val3 bigint,
317 + @int64Val4 bigint,
318 + @stringVal1 nvarchar(128),
319 + @stringVal2 nvarchar(128),
320 + @stringVal3 nvarchar(128),
321 + @stringVal4 nvarchar(128)
322 + as
323 + begin
324 + set nocount on;
325 +
326 + insert into AssertLog (
327 + timeStamp, planetId, serverType, logType,
328 + intVal1, intVal2, intVal3, intVal4, intVal5, intVal6, intVal7, intVal8, intVal9, intVal10, intVal11, intVal12, intVal13, intVal14, intVal15,
329 + int64Val1, int64Val2, int64Val3, int64Val4,
330 + stringVal1, stringVal2, stringVal3, stringVal4 )
331 + values (
332 + @timeStamp, @planetId, @serverType, @logType,
333 + @intVal1, @intVal2, @intVal3, @intVal4, @intVal5, @intVal6, @intVal7, @intVal8, @intVal9, @intVal10, @intVal11, @intVal12, @intVal13, @intVal14, @intVal15,
334 + @int64Val1, @int64Val2, @int64Val3, @int64Val4,
335 + @stringVal1, @stringVal2, @stringVal3, @stringVal4 );
336 + end
337 + GO
Nowsze Starsze

Obsługiwane przez Opengist ⋅ Load: 15ms⋅

Polski