· 9 years ago · Dec 05, 2016, 02:40 PM
1USE [CarParts]
2GO
3/****** Object: StoredProcedure [dbo].[clean_tests] Script Date: 12/5/2016 4:37:57 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8-- =============================================
9-- Author: Name
10-- Create date:
11-- Description:
12-- =============================================
13ALTER PROCEDURE [dbo].[clean_tests]
14 -- Add the parameters for the stored procedure here
15
16AS
17BEGIN
18 -- SET NOCOUNT ON added to prevent extra result sets from
19 -- interfering with SELECT statements.
20 SET NOCOUNT ON;
21
22 -- Insert statements for procedure here
23
24if exists (select * from dbo.sysobjects where id = object_id(N'[FK_TestRunTables_Tables]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
25
26ALTER TABLE [TestRunTables] DROP CONSTRAINT FK_TestRunTables_Tables
27
28
29
30
31
32if exists (select * from dbo.sysobjects where id = object_id(N'[FK_TestTables_Tables]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
33
34ALTER TABLE [TestTables] DROP CONSTRAINT FK_TestTables_Tables
35
36
37
38
39
40if exists (select * from dbo.sysobjects where id = object_id(N'[FK_TestRunTables_TestRuns]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
41
42ALTER TABLE [TestRunTables] DROP CONSTRAINT FK_TestRunTables_TestRuns
43
44
45
46
47
48if exists (select * from dbo.sysobjects where id = object_id(N'[FK_TestRunViews_TestRuns]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
49
50ALTER TABLE [TestRunViews] DROP CONSTRAINT FK_TestRunViews_TestRuns
51
52
53
54
55if exists (select * from dbo.sysobjects where id = object_id(N'[FK_TestTables_Tests]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
56
57ALTER TABLE [TestTables] DROP CONSTRAINT FK_TestTables_Tests
58
59
60
61
62if exists (select * from dbo.sysobjects where id = object_id(N'[FK_TestViews_Tests]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
63
64ALTER TABLE [TestViews] DROP CONSTRAINT FK_TestViews_Tests
65
66
67
68
69if exists (select * from dbo.sysobjects where id = object_id(N'[FK_TestRunViews_Views]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
70
71ALTER TABLE [TestRunViews] DROP CONSTRAINT FK_TestRunViews_Views
72
73
74
75
76if exists (select * from dbo.sysobjects where id = object_id(N'[FK_TestViews_Views]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
77
78ALTER TABLE [TestViews] DROP CONSTRAINT FK_TestViews_Views
79
80
81
82
83if exists (select * from dbo.sysobjects where id = object_id(N'[Tables]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
84
85drop table [Tables]
86
87
88
89
90if exists (select * from dbo.sysobjects where id = object_id(N'[TestRunTables]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
91
92drop table [TestRunTables]
93
94
95
96
97if exists (select * from dbo.sysobjects where id = object_id(N'[TestRunViews]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
98
99drop table [TestRunViews]
100
101
102
103if exists (select * from dbo.sysobjects where id = object_id(N'[TestRuns]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
104
105drop table [TestRuns]
106
107
108
109
110if exists (select * from dbo.sysobjects where id = object_id(N'[TestTables]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
111
112drop table [TestTables]
113
114
115
116
117if exists (select * from dbo.sysobjects where id = object_id(N'[TestViews]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
118
119drop table [TestViews]
120
121
122
123
124if exists (select * from dbo.sysobjects where id = object_id(N'[Tests]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
125
126drop table [Tests]
127
128
129
130
131if exists (select * from dbo.sysobjects where id = object_id(N'[Views]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
132
133drop table [Views]
134
135
136
137
138CREATE TABLE [Tables] (
139
140 [TableID] [int] IDENTITY (1, 1) NOT NULL ,
141
142 [Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
143
144) ON [PRIMARY]
145
146
147
148
149CREATE TABLE [TestRunTables] (
150
151 [TestRunID] [int] NOT NULL ,
152
153 [TableID] [int] NOT NULL ,
154
155 [StartAt] [datetime] NOT NULL ,
156
157 [EndAt] [datetime] NOT NULL
158
159) ON [PRIMARY]
160
161
162
163
164CREATE TABLE [TestRunViews] (
165
166 [TestRunID] [int] NOT NULL ,
167
168 [ViewID] [int] NOT NULL ,
169
170 [StartAt] [datetime] NOT NULL ,
171
172 [EndAt] [datetime] NOT NULL
173
174) ON [PRIMARY]
175
176
177
178
179CREATE TABLE [TestRuns] (
180
181 [TestRunID] [int] IDENTITY (1, 1) NOT NULL ,
182
183 [Description] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
184
185 [StartAt] [datetime] NULL ,
186
187 [EndAt] [datetime] NULL
188
189) ON [PRIMARY]
190
191
192
193
194CREATE TABLE [TestTables] (
195
196 [TestID] [int] NOT NULL ,
197
198 [TableID] [int] NOT NULL ,
199
200 [NoOfRows] [int] NOT NULL ,
201
202 [Position] [int] NOT NULL
203
204) ON [PRIMARY]
205
206
207
208
209CREATE TABLE [TestViews] (
210
211 [TestID] [int] NOT NULL ,
212
213 [ViewID] [int] NOT NULL
214
215) ON [PRIMARY]
216
217
218
219
220CREATE TABLE [Tests] (
221
222 [TestID] [int] IDENTITY (1, 1) NOT NULL ,
223
224 [Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
225
226) ON [PRIMARY]
227
228
229
230
231CREATE TABLE [Views] (
232
233 [ViewID] [int] IDENTITY (1, 1) NOT NULL ,
234
235 [Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
236
237) ON [PRIMARY]
238
239
240
241
242ALTER TABLE [Tables] WITH NOCHECK ADD
243
244 CONSTRAINT [PK_Tables] PRIMARY KEY CLUSTERED
245
246 (
247
248 [TableID]
249
250 ) ON [PRIMARY]
251
252
253
254
255ALTER TABLE [TestRunTables] WITH NOCHECK ADD
256
257 CONSTRAINT [PK_TestRunTables] PRIMARY KEY CLUSTERED
258
259 (
260
261 [TestRunID],
262
263 [TableID]
264
265 ) ON [PRIMARY]
266
267
268
269
270ALTER TABLE [TestRunViews] WITH NOCHECK ADD
271
272 CONSTRAINT [PK_TestRunViews] PRIMARY KEY CLUSTERED
273
274 (
275
276 [TestRunID],
277
278 [ViewID]
279
280 ) ON [PRIMARY]
281
282
283
284
285ALTER TABLE [TestRuns] WITH NOCHECK ADD
286
287 CONSTRAINT [PK_TestRuns] PRIMARY KEY CLUSTERED
288
289 (
290
291 [TestRunID]
292
293 ) ON [PRIMARY]
294
295
296
297
298ALTER TABLE [TestTables] WITH NOCHECK ADD
299
300 CONSTRAINT [PK_TestTables] PRIMARY KEY CLUSTERED
301
302 (
303
304 [TestID],
305
306 [TableID]
307
308 ) ON [PRIMARY]
309
310
311
312
313ALTER TABLE [TestViews] WITH NOCHECK ADD
314
315 CONSTRAINT [PK_TestViews] PRIMARY KEY CLUSTERED
316
317 (
318
319 [TestID],
320
321 [ViewID]
322
323 ) ON [PRIMARY]
324
325
326
327
328ALTER TABLE [Tests] WITH NOCHECK ADD
329
330 CONSTRAINT [PK_Tests] PRIMARY KEY CLUSTERED
331
332 (
333
334 [TestID]
335
336 ) ON [PRIMARY]
337
338
339
340
341ALTER TABLE [Views] WITH NOCHECK ADD
342
343 CONSTRAINT [PK_Views] PRIMARY KEY CLUSTERED
344
345 (
346
347 [ViewID]
348
349 ) ON [PRIMARY]
350
351
352
353
354ALTER TABLE [TestRunTables] ADD
355
356 CONSTRAINT [FK_TestRunTables_Tables] FOREIGN KEY
357
358 (
359
360 [TableID]
361
362 ) REFERENCES [Tables] (
363
364 [TableID]
365
366 ) ON DELETE CASCADE ON UPDATE CASCADE ,
367
368 CONSTRAINT [FK_TestRunTables_TestRuns] FOREIGN KEY
369
370 (
371
372 [TestRunID]
373
374 ) REFERENCES [TestRuns] (
375
376 [TestRunID]
377
378 ) ON DELETE CASCADE ON UPDATE CASCADE
379
380
381
382
383ALTER TABLE [TestRunViews] ADD
384
385 CONSTRAINT [FK_TestRunViews_TestRuns] FOREIGN KEY
386
387 (
388
389 [TestRunID]
390
391 ) REFERENCES [TestRuns] (
392
393 [TestRunID]
394
395 ) ON DELETE CASCADE ON UPDATE CASCADE ,
396
397 CONSTRAINT [FK_TestRunViews_Views] FOREIGN KEY
398
399 (
400
401 [ViewID]
402
403 ) REFERENCES [Views] (
404
405 [ViewID]
406
407 ) ON DELETE CASCADE ON UPDATE CASCADE
408
409
410
411
412ALTER TABLE [TestTables] ADD
413
414 CONSTRAINT [FK_TestTables_Tables] FOREIGN KEY
415
416 (
417
418 [TableID]
419
420 ) REFERENCES [Tables] (
421
422 [TableID]
423
424 ) ON DELETE CASCADE ON UPDATE CASCADE ,
425
426 CONSTRAINT [FK_TestTables_Tests] FOREIGN KEY
427
428 (
429
430 [TestID]
431
432 ) REFERENCES [Tests] (
433
434 [TestID]
435
436 ) ON DELETE CASCADE ON UPDATE CASCADE
437
438
439
440ALTER TABLE [TestViews] ADD
441
442 CONSTRAINT [FK_TestViews_Tests] FOREIGN KEY
443
444 (
445
446 [TestID]
447
448 ) REFERENCES [Tests] (
449
450 [TestID]
451
452 ),
453
454 CONSTRAINT [FK_TestViews_Views] FOREIGN KEY
455
456 (
457
458 [ViewID]
459
460 ) REFERENCES [Views] (
461
462 [ViewID]
463
464 )
465
466
467
468
469
470END