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