· 9 years ago · Dec 06, 2016, 04:38 PM
1/*
2Deployment script for ParkInspect
3
4This code was generated by a tool.
5Changes to this file may cause incorrect behavior and will be lost if
6the code is regenerated.
7*/
8
9GO
10SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
11
12SET NUMERIC_ROUNDABORT OFF;
13
14
15GO
16:setvar DatabaseName "ParkInspect_Arian"
17:setvar DefaultFilePrefix "ParkInspect_Arian"
18:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\"
19:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\"
20
21GO
22:on error exit
23GO
24/*
25Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
26To re-enable the script after enabling SQLCMD mode, execute the following:
27SET NOEXEC OFF;
28*/
29:setvar __IsSqlCmdEnabled "True"
30GO
31IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
32 BEGIN
33 PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
34 SET NOEXEC ON;
35 END
36
37
38GO
39USE [$(DatabaseName)];
40
41
42GO
43IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
44 EXECUTE sp_fulltext_database 'enable';
45
46
47GO
48PRINT N'Creating [dbo].[Address]...';
49
50
51GO
52CREATE TABLE [dbo].[Address] (
53 [Id] INT IDENTITY (1, 1) NOT NULL,
54 [StreetName] VARCHAR (50) NOT NULL,
55 [HouseNumber] VARCHAR (50) NULL,
56 [ZipCode] VARCHAR (50) NOT NULL,
57 [City] VARCHAR (50) NOT NULL,
58 PRIMARY KEY CLUSTERED ([Id] ASC)
59);
60
61
62GO
63PRINT N'Creating [dbo].[Assignment]...';
64
65
66GO
67CREATE TABLE [dbo].[Assignment] (
68 [Id] INT IDENTITY (1, 1) NOT NULL,
69 [Title] VARCHAR (50) NULL,
70 [ParkingLotId] INT NOT NULL,
71 [CustomerRequestId] INT NOT NULL,
72 [StateId] INT NOT NULL,
73 PRIMARY KEY CLUSTERED ([Id] ASC)
74);
75
76
77GO
78PRINT N'Creating [dbo].[AssignmentState]...';
79
80
81GO
82CREATE TABLE [dbo].[AssignmentState] (
83 [Id] INT IDENTITY (1, 1) NOT NULL,
84 [State] VARCHAR (50) NOT NULL,
85 PRIMARY KEY CLUSTERED ([Id] ASC)
86);
87
88
89GO
90PRINT N'Creating [dbo].[Customer]...';
91
92
93GO
94CREATE TABLE [dbo].[Customer] (
95 [Id] INT IDENTITY (1, 1) NOT NULL,
96 [Name] VARCHAR (50) NOT NULL,
97 [FirstName] VARCHAR (50) NULL,
98 [Infix] VARCHAR (50) NULL,
99 [LastName] VARCHAR (50) NULL,
100 [AddressId] INT NULL,
101 [Email] VARCHAR (50) NULL,
102 [PhoneNumber] VARCHAR (50) NULL,
103 [Comment] VARCHAR (MAX) NULL,
104 PRIMARY KEY CLUSTERED ([Id] ASC)
105);
106
107
108GO
109PRINT N'Creating [dbo].[CustomerPhoto]...';
110
111
112GO
113CREATE TABLE [dbo].[CustomerPhoto] (
114 [CustomerId] INT NOT NULL,
115 [File] VARBINARY (MAX) NOT NULL,
116 PRIMARY KEY CLUSTERED ([CustomerId] ASC)
117);
118
119
120GO
121PRINT N'Creating [dbo].[CustomerRequest]...';
122
123
124GO
125CREATE TABLE [dbo].[CustomerRequest] (
126 [Id] INT IDENTITY (1, 1) NOT NULL,
127 [CustomerId] INT NOT NULL,
128 [Name] VARCHAR (MAX) NULL,
129 PRIMARY KEY CLUSTERED ([Id] ASC)
130);
131
132
133GO
134PRINT N'Creating [dbo].[Inspection]...';
135
136
137GO
138CREATE TABLE [dbo].[Inspection] (
139 [Id] INT IDENTITY (1, 1) NOT NULL,
140 [Title] VARCHAR (50) NOT NULL,
141 [Remarks] VARCHAR (MAX) NULL,
142 [AssignmentId] INT NOT NULL,
143 [InspectorId] INT NULL,
144 [QuestionnaireId] INT NULL,
145 [StateId] INT NOT NULL,
146 PRIMARY KEY CLUSTERED ([Id] ASC)
147);
148
149
150GO
151PRINT N'Creating [dbo].[InspectionPhoto]...';
152
153
154GO
155CREATE TABLE [dbo].[InspectionPhoto] (
156 [Id] INT IDENTITY (1, 1) NOT NULL,
157 [InspectionId] INT NOT NULL,
158 [File] VARBINARY (MAX) NOT NULL,
159 PRIMARY KEY CLUSTERED ([Id] ASC)
160);
161
162
163GO
164PRINT N'Creating [dbo].[InspectionState]...';
165
166
167GO
168CREATE TABLE [dbo].[InspectionState] (
169 [Id] INT IDENTITY (1, 1) NOT NULL,
170 [State] VARCHAR (50) NOT NULL,
171 PRIMARY KEY CLUSTERED ([Id] ASC)
172);
173
174
175GO
176PRINT N'Creating [dbo].[Inspector]...';
177
178
179GO
180CREATE TABLE [dbo].[Inspector] (
181 [UserId] INT NOT NULL,
182 PRIMARY KEY CLUSTERED ([UserId] ASC)
183);
184
185
186GO
187PRINT N'Creating [dbo].[InspectorRegion]...';
188
189
190GO
191CREATE TABLE [dbo].[InspectorRegion] (
192 [InspectorId] INT NOT NULL,
193 [RegionId] INT NOT NULL,
194 PRIMARY KEY CLUSTERED ([InspectorId] ASC, [RegionId] ASC)
195);
196
197
198GO
199PRINT N'Creating [dbo].[Manager]...';
200
201
202GO
203CREATE TABLE [dbo].[Manager] (
204 [UserId] INT NOT NULL,
205 PRIMARY KEY CLUSTERED ([UserId] ASC)
206);
207
208
209GO
210PRINT N'Creating [dbo].[ParkingLot]...';
211
212
213GO
214CREATE TABLE [dbo].[ParkingLot] (
215 [Id] INT IDENTITY (1, 1) NOT NULL,
216 [RegionId] INT NOT NULL,
217 [Name] VARCHAR (50) NOT NULL,
218 [AddressId] INT NOT NULL,
219 [Latitude] DECIMAL (10, 6) NULL,
220 [Longitude] DECIMAL (18, 6) NULL,
221 PRIMARY KEY CLUSTERED ([Id] ASC)
222);
223
224
225GO
226PRINT N'Creating [dbo].[Question]...';
227
228
229GO
230CREATE TABLE [dbo].[Question] (
231 [Id] INT IDENTITY (1, 1) NOT NULL,
232 [TypeId] INT NOT NULL,
233 PRIMARY KEY CLUSTERED ([Id] ASC)
234);
235
236
237GO
238PRINT N'Creating [dbo].[Questionnaire]...';
239
240
241GO
242CREATE TABLE [dbo].[Questionnaire] (
243 [Id] INT IDENTITY (1, 1) NOT NULL,
244 [IsTemplate] BIT NULL,
245 [Name] VARCHAR (50) NOT NULL,
246 PRIMARY KEY CLUSTERED ([Id] ASC)
247);
248
249
250GO
251PRINT N'Creating [dbo].[QuestionnaireQuestion]...';
252
253
254GO
255CREATE TABLE [dbo].[QuestionnaireQuestion] (
256 [QuestionnaireId] INT NOT NULL,
257 [QuestionVersionId] INT NOT NULL,
258 PRIMARY KEY CLUSTERED ([QuestionVersionId] ASC, [QuestionnaireId] ASC)
259);
260
261
262GO
263PRINT N'Creating [dbo].[QuestionType]...';
264
265
266GO
267CREATE TABLE [dbo].[QuestionType] (
268 [Id] INT IDENTITY (1, 1) NOT NULL,
269 [type] VARCHAR (50) NOT NULL,
270 PRIMARY KEY CLUSTERED ([Id] ASC)
271);
272
273
274GO
275PRINT N'Creating [dbo].[QuestionVersion]...';
276
277
278GO
279CREATE TABLE [dbo].[QuestionVersion] (
280 [Id] INT IDENTITY (1, 1) NOT NULL,
281 [question] VARCHAR (200) NOT NULL,
282 [version] INT NOT NULL,
283 [QuestionId] INT NOT NULL,
284 PRIMARY KEY CLUSTERED ([Id] ASC)
285);
286
287
288GO
289PRINT N'Creating [dbo].[Region]...';
290
291
292GO
293CREATE TABLE [dbo].[Region] (
294 [Id] INT IDENTITY (1, 1) NOT NULL,
295 [Province] VARCHAR (50) NOT NULL,
296 PRIMARY KEY CLUSTERED ([Id] ASC)
297);
298
299
300GO
301PRINT N'Creating [dbo].[Result]...';
302
303
304GO
305CREATE TABLE [dbo].[Result] (
306 [Id] INT IDENTITY (1, 1) NOT NULL,
307 [InspectionId] INT NOT NULL,
308 [QuestionVersionId] INT NULL,
309 [Answer] VARCHAR (MAX) NULL,
310 PRIMARY KEY CLUSTERED ([Id] ASC)
311);
312
313
314GO
315PRINT N'Creating [dbo].[Schedule]...';
316
317
318GO
319CREATE TABLE [dbo].[Schedule] (
320 [InspectionId] INT NOT NULL,
321 [InspectorId] INT NULL,
322 [Date] DATETIME NOT NULL,
323 CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED ([InspectionId] ASC),
324 UNIQUE NONCLUSTERED ([InspectionId] ASC)
325);
326
327
328GO
329PRINT N'Creating [dbo].[User]...';
330
331
332GO
333CREATE TABLE [dbo].[User] (
334 [Id] INT IDENTITY (1, 1) NOT NULL,
335 [Email] VARCHAR (MAX) NOT NULL,
336 [Password] VARCHAR (50) NOT NULL,
337 [FirstName] VARCHAR (50) NOT NULL,
338 [Infix] VARCHAR (50) NULL,
339 [AddressId] INT NOT NULL,
340 [LastName] VARCHAR (50) NOT NULL,
341 [PhoneNumber] VARCHAR (50) NOT NULL,
342 [UserTypeId] INT NOT NULL,
343 [Comment] VARCHAR (MAX) NULL,
344 PRIMARY KEY CLUSTERED ([Id] ASC)
345);
346
347
348GO
349PRINT N'Creating [dbo].[UserPhoto]...';
350
351
352GO
353CREATE TABLE [dbo].[UserPhoto] (
354 [UserId] INT NOT NULL,
355 [File] VARBINARY (MAX) NULL,
356 PRIMARY KEY CLUSTERED ([UserId] ASC)
357);
358
359
360GO
361PRINT N'Creating [dbo].[UserType]...';
362
363
364GO
365CREATE TABLE [dbo].[UserType] (
366 [Id] INT IDENTITY (1, 1) NOT NULL,
367 [Type] VARCHAR (50) NOT NULL,
368 PRIMARY KEY CLUSTERED ([Id] ASC)
369);
370
371
372GO
373PRINT N'Creating [dbo].[FK_InspectionRequest_ParkingLotId]...';
374
375
376GO
377ALTER TABLE [dbo].[Assignment]
378 ADD CONSTRAINT [FK_InspectionRequest_ParkingLotId] FOREIGN KEY ([ParkingLotId]) REFERENCES [dbo].[ParkingLot] ([Id]);
379
380
381GO
382PRINT N'Creating [dbo].[FK_InspectionRequest_CustomerRequestId]...';
383
384
385GO
386ALTER TABLE [dbo].[Assignment]
387 ADD CONSTRAINT [FK_InspectionRequest_CustomerRequestId] FOREIGN KEY ([CustomerRequestId]) REFERENCES [dbo].[CustomerRequest] ([Id]);
388
389
390GO
391PRINT N'Creating [dbo].[FK_InspectionRequest_StateId]...';
392
393
394GO
395ALTER TABLE [dbo].[Assignment]
396 ADD CONSTRAINT [FK_InspectionRequest_StateId] FOREIGN KEY ([StateId]) REFERENCES [dbo].[AssignmentState] ([Id]);
397
398
399GO
400PRINT N'Creating [dbo].[FK_Customer_AddressId]...';
401
402
403GO
404ALTER TABLE [dbo].[Customer]
405 ADD CONSTRAINT [FK_Customer_AddressId] FOREIGN KEY ([AddressId]) REFERENCES [dbo].[Address] ([Id]);
406
407
408GO
409PRINT N'Creating [dbo].[FK_CustomerPhoto_CustomerId]...';
410
411
412GO
413ALTER TABLE [dbo].[CustomerPhoto]
414 ADD CONSTRAINT [FK_CustomerPhoto_CustomerId] FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customer] ([Id]);
415
416
417GO
418PRINT N'Creating [dbo].[FK_CustomerRequest_CustomerId]...';
419
420
421GO
422ALTER TABLE [dbo].[CustomerRequest]
423 ADD CONSTRAINT [FK_CustomerRequest_CustomerId] FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customer] ([Id]);
424
425
426GO
427PRINT N'Creating [dbo].[FK_Inspection_StateID]...';
428
429
430GO
431ALTER TABLE [dbo].[Inspection]
432 ADD CONSTRAINT [FK_Inspection_StateID] FOREIGN KEY ([StateId]) REFERENCES [dbo].[InspectionState] ([Id]);
433
434
435GO
436PRINT N'Creating [dbo].[FK_Inspection_InspectorId]...';
437
438
439GO
440ALTER TABLE [dbo].[Inspection]
441 ADD CONSTRAINT [FK_Inspection_InspectorId] FOREIGN KEY ([InspectorId]) REFERENCES [dbo].[Inspector] ([UserId]);
442
443
444GO
445PRINT N'Creating [dbo].[FK_Inspection_AssignmentId]...';
446
447
448GO
449ALTER TABLE [dbo].[Inspection]
450 ADD CONSTRAINT [FK_Inspection_AssignmentId] FOREIGN KEY ([AssignmentId]) REFERENCES [dbo].[Assignment] ([Id]);
451
452
453GO
454PRINT N'Creating [dbo].[FK_Inspection_QuestionnaireId]...';
455
456
457GO
458ALTER TABLE [dbo].[Inspection]
459 ADD CONSTRAINT [FK_Inspection_QuestionnaireId] FOREIGN KEY ([QuestionnaireId]) REFERENCES [dbo].[Questionnaire] ([Id]);
460
461
462GO
463PRINT N'Creating [dbo].[FK_Inspectionphoto_InspectionId]...';
464
465
466GO
467ALTER TABLE [dbo].[InspectionPhoto]
468 ADD CONSTRAINT [FK_Inspectionphoto_InspectionId] FOREIGN KEY ([InspectionId]) REFERENCES [dbo].[Inspection] ([Id]);
469
470
471GO
472PRINT N'Creating [dbo].[FK_Inspector_InspectorId]...';
473
474
475GO
476ALTER TABLE [dbo].[Inspector]
477 ADD CONSTRAINT [FK_Inspector_InspectorId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([Id]);
478
479
480GO
481PRINT N'Creating [dbo].[FK_InspectorRegion_InspectorId]...';
482
483
484GO
485ALTER TABLE [dbo].[InspectorRegion]
486 ADD CONSTRAINT [FK_InspectorRegion_InspectorId] FOREIGN KEY ([InspectorId]) REFERENCES [dbo].[Inspector] ([UserId]);
487
488
489GO
490PRINT N'Creating [dbo].[FK_InspectorRegion_RegionId]...';
491
492
493GO
494ALTER TABLE [dbo].[InspectorRegion]
495 ADD CONSTRAINT [FK_InspectorRegion_RegionId] FOREIGN KEY ([RegionId]) REFERENCES [dbo].[Region] ([Id]);
496
497
498GO
499PRINT N'Creating [dbo].[FK_Manager_UserId]...';
500
501
502GO
503ALTER TABLE [dbo].[Manager]
504 ADD CONSTRAINT [FK_Manager_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([Id]);
505
506
507GO
508PRINT N'Creating [dbo].[FK_ParkingLot_RegionId]...';
509
510
511GO
512ALTER TABLE [dbo].[ParkingLot]
513 ADD CONSTRAINT [FK_ParkingLot_RegionId] FOREIGN KEY ([RegionId]) REFERENCES [dbo].[Region] ([Id]);
514
515
516GO
517PRINT N'Creating [dbo].[FK_ParkingLot_AddressId]...';
518
519
520GO
521ALTER TABLE [dbo].[ParkingLot]
522 ADD CONSTRAINT [FK_ParkingLot_AddressId] FOREIGN KEY ([AddressId]) REFERENCES [dbo].[Address] ([Id]);
523
524
525GO
526PRINT N'Creating [dbo].[FK_Question_TypeId]...';
527
528
529GO
530ALTER TABLE [dbo].[Question]
531 ADD CONSTRAINT [FK_Question_TypeId] FOREIGN KEY ([TypeId]) REFERENCES [dbo].[QuestionType] ([Id]);
532
533
534GO
535PRINT N'Creating [dbo].[FK_QuestionnaireQuestions_QuestionnaireId]...';
536
537
538GO
539ALTER TABLE [dbo].[QuestionnaireQuestion]
540 ADD CONSTRAINT [FK_QuestionnaireQuestions_QuestionnaireId] FOREIGN KEY ([QuestionnaireId]) REFERENCES [dbo].[Questionnaire] ([Id]);
541
542
543GO
544PRINT N'Creating [dbo].[FK_QuestionnaireQuestions_QuestionVersionId]...';
545
546
547GO
548ALTER TABLE [dbo].[QuestionnaireQuestion]
549 ADD CONSTRAINT [FK_QuestionnaireQuestions_QuestionVersionId] FOREIGN KEY ([QuestionVersionId]) REFERENCES [dbo].[QuestionVersion] ([Id]);
550
551
552GO
553PRINT N'Creating [dbo].[FK_QuestionVersion_QuestionId]...';
554
555
556GO
557ALTER TABLE [dbo].[QuestionVersion]
558 ADD CONSTRAINT [FK_QuestionVersion_QuestionId] FOREIGN KEY ([QuestionId]) REFERENCES [dbo].[Question] ([Id]);
559
560
561GO
562PRINT N'Creating [dbo].[FK_Result_QuestionVersionId]...';
563
564
565GO
566ALTER TABLE [dbo].[Result]
567 ADD CONSTRAINT [FK_Result_QuestionVersionId] FOREIGN KEY ([QuestionVersionId]) REFERENCES [dbo].[QuestionVersion] ([Id]);
568
569
570GO
571PRINT N'Creating [dbo].[FK_Result_InspectionId]...';
572
573
574GO
575ALTER TABLE [dbo].[Result]
576 ADD CONSTRAINT [FK_Result_InspectionId] FOREIGN KEY ([InspectionId]) REFERENCES [dbo].[Schedule] ([InspectionId]);
577
578
579GO
580PRINT N'Creating [dbo].[FK_Schedule_InspectionId]...';
581
582
583GO
584ALTER TABLE [dbo].[Schedule]
585 ADD CONSTRAINT [FK_Schedule_InspectionId] FOREIGN KEY ([InspectionId]) REFERENCES [dbo].[Inspection] ([Id]);
586
587
588GO
589PRINT N'Creating [dbo].[FK_Schedule_InspectorId]...';
590
591
592GO
593ALTER TABLE [dbo].[Schedule]
594 ADD CONSTRAINT [FK_Schedule_InspectorId] FOREIGN KEY ([InspectorId]) REFERENCES [dbo].[Inspector] ([UserId]);
595
596
597GO
598PRINT N'Creating [dbo].[FK_User_AddressId]...';
599
600
601GO
602ALTER TABLE [dbo].[User]
603 ADD CONSTRAINT [FK_User_AddressId] FOREIGN KEY ([AddressId]) REFERENCES [dbo].[Address] ([Id]);
604
605
606GO
607PRINT N'Creating [dbo].[FK_User_UserTypeId]...';
608
609
610GO
611ALTER TABLE [dbo].[User]
612 ADD CONSTRAINT [FK_User_UserTypeId] FOREIGN KEY ([UserTypeId]) REFERENCES [dbo].[UserType] ([Id]);
613
614
615GO
616PRINT N'Creating [dbo].[FK_UserPhoto_UserId]...';
617
618
619GO
620ALTER TABLE [dbo].[UserPhoto]
621 ADD CONSTRAINT [FK_UserPhoto_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([Id]);
622
623
624GO
625PRINT N'Creating [dbo].[chk_Email_or_PhoneNumber_filled]...';
626
627
628GO
629ALTER TABLE [dbo].[Customer]
630 ADD CONSTRAINT [chk_Email_or_PhoneNumber_filled] CHECK (Email IS NOT NULL OR PhoneNumber IS NOT NULL);
631
632
633GO
634-- create UpdatedAt and CreatedAt columns for all tables
635
636EXEC sys.sp_MSforeachtable 'ALTER TABLE ? ADD UpdatedAt DateTime NULL'
637EXEC sys.sp_MSforeachtable 'ALTER TABLE ? ADD CreatedAt DateTime NULL'
638
639-- add triggers for all UpdatedAt columns
640
641DECLARE @statement NVARCHAR(MAX)
642DECLARE table_cursor CURSOR FOR
643
644-- create queries for all tables
645
646SELECT N'
647 CREATE TRIGGER trg_' + t.Name + '_Update ON ' + ObjectName + '
648 AFTER UPDATE
649 AS
650 BEGIN
651 UPDATE t
652 SET UpdatedAt = GETDATE()
653 FROM ' + o.ObjectName + ' AS t
654 INNER JOIN inserted AS i
655 ON ' +
656 STUFF((SELECT ' AND t.' + QUOTENAME(c.Name) + ' = i.' + QUOTENAME(c.Name)
657 FROM sys.index_columns AS ic
658 INNER JOIN sys.columns AS c
659 ON c.object_id = ic.object_id
660 AND c.column_id = ic.column_id
661 WHERE ic.object_id = t.object_id
662 AND ic.index_id = ix.index_id
663 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 4, '') + ';
664 END;'
665FROM sys.tables AS t
666 INNER JOIN sys.indexes AS ix
667 ON ix.object_id = t.object_id
668 AND ix.is_primary_key = 1
669 CROSS APPLY (SELECT QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + '.' + QUOTENAME(t.name)) o (ObjectName)
670WHERE EXISTS
671 ( SELECT 1
672 FROM sys.columns AS c
673 WHERE c.Name = 'UpdatedAt'
674 AND c.object_id = t.object_id
675 );
676
677-- excute queries
678
679OPEN table_cursor
680FETCH NEXT FROM table_cursor into @statement
681WHILE @@FETCH_STATUS = 0
682BEGIN
683SELECT @statement
684EXEC sp_executesql @statement
685FETCH NEXT FROM table_cursor INTO @statement
686END
687CLOSE table_cursor
688DEALLOCATE table_cursor
689
690-- create queries voor update
691
692DECLARE table_cursor_2 CURSOR FOR
693SELECT N'
694 CREATE TRIGGER trg_' + t.Name + '_Insert ON ' + ObjectName + '
695 AFTER Insert
696 AS
697 BEGIN
698 UPDATE t
699 SET CreatedAt = GETDATE()
700 FROM ' + o.ObjectName + ' AS t
701 INNER JOIN inserted AS i
702 ON ' +
703 STUFF((SELECT ' AND t.' + QUOTENAME(c.Name) + ' = i.' + QUOTENAME(c.Name)
704 FROM sys.index_columns AS ic
705 INNER JOIN sys.columns AS c
706 ON c.object_id = ic.object_id
707 AND c.column_id = ic.column_id
708 WHERE ic.object_id = t.object_id
709 AND ic.index_id = ix.index_id
710 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 4, '') + ';
711 END;'
712FROM sys.tables AS t
713 INNER JOIN sys.indexes AS ix
714 ON ix.object_id = t.object_id
715 AND ix.is_primary_key = 1
716 CROSS APPLY (SELECT QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + '.' + QUOTENAME(t.name)) o (ObjectName)
717WHERE EXISTS
718 ( SELECT 1
719 FROM sys.columns AS c
720 WHERE c.Name = 'UpdatedAt'
721 AND c.object_id = t.object_id
722 );
723
724-- excute queries
725
726OPEN table_cursor_2
727FETCH NEXT FROM table_cursor_2 into @statement
728WHILE @@FETCH_STATUS = 0
729BEGIN
730SELECT @statement
731EXEC sp_executesql @statement
732FETCH NEXT FROM table_cursor_2 INTO @statement
733END
734CLOSE table_cursor_2
735DEALLOCATE table_cursor_2
736
737-- add LookUpTables values
738
739INSERT INTO [UserType] ([Type]) VALUES ('Manager');
740INSERT INTO [UserType] ([Type]) VALUES ('Inspector');
741
742INSERT INTO [InspectionState] ([State]) VALUES ('Requested');
743INSERT INTO [InspectionState] ([State]) VALUES ('Accepted');
744INSERT INTO [InspectionState] ([State]) VALUES ('Denied');
745INSERT INTO [InspectionState] ([State]) VALUES ('Finished');
746
747INSERT INTO [AssignmentState] ([State]) VALUES ('Requested');
748INSERT INTO [AssignmentState] ([State]) VALUES ('Accepted');
749INSERT INTO [AssignmentState] ([State]) VALUES ('Denied');
750INSERT INTO [AssignmentState] ([State]) VALUES ('Finished');
751
752INSERT INTO [Region] ([Province]) VALUES ('Drenthe');
753INSERT INTO [Region] ([Province]) VALUES ('Flevoland');
754INSERT INTO [Region] ([Province]) VALUES ('Friesland');
755INSERT INTO [Region] ([Province]) VALUES ('Gelderland');
756INSERT INTO [Region] ([Province]) VALUES ('Groningen');
757INSERT INTO [Region] ([Province]) VALUES ('Limburg');
758INSERT INTO [Region] ([Province]) VALUES ('Noord-Brabant');
759INSERT INTO [Region] ([Province]) VALUES ('Noord-Holland');
760INSERT INTO [Region] ([Province]) VALUES ('Overijssel');
761INSERT INTO [Region] ([Province]) VALUES ('Utrecht');
762INSERT INTO [Region] ([Province]) VALUES ('Zeeland');
763INSERT INTO [Region] ([Province]) VALUES ('Zuid-Holland');
764
765INSERT INTO [Address] ([City], [HouseNumber], [StreetName], [ZipCode]) VALUES ('s-Hertogenbosch', 215, 'onderwijsboulevard', '5223 DE');
766
767INSERT INTO [User] ([AddressId], [UserTypeId], [Email], [FirstName], [LastName], [Password], [PhoneNumber]) VALUES (
768 (SELECT min([Id]) FROM [Address]),
769 (SELECT [ID] FROM [UserType] WHERE [Type] = 'Manager'),
770 'admin@parkinspect.nl',
771 'Piet',
772 'Klerx',
773 'pass',
774 '0612345678');
775GO
776
777GO
778DECLARE @VarDecimalSupported AS BIT;
779
780SELECT @VarDecimalSupported = 0;
781
782IF ((ServerProperty(N'EngineEdition') = 3)
783 AND (((@@microsoftversion / power(2, 24) = 9)
784 AND (@@microsoftversion & 0xffff >= 3024))
785 OR ((@@microsoftversion / power(2, 24) = 10)
786 AND (@@microsoftversion & 0xffff >= 1600))))
787 SELECT @VarDecimalSupported = 1;
788
789IF (@VarDecimalSupported > 0)
790 BEGIN
791 EXECUTE sp_db_vardecimal_storage_format N'$(DatabaseName)', 'ON';
792 END
793
794
795GO
796ALTER DATABASE [$(DatabaseName)]
797 SET RESTRICTED_USER
798 WITH ROLLBACK IMMEDIATE;
799
800
801GO
802PRINT N'Update complete.';
803
804
805GO