-
Notifications
You must be signed in to change notification settings - Fork 0
/
createdatabase
145 lines (107 loc) · 6.09 KB
/
createdatabase
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
Note: the Description property of each column is provided for some columns when the column title is not entirely self-explanatory.
Note: default values are provided whenever possible.
Note: editor and modified track the last modification made to the row, storing user name and datetime stamp respectively.
Note: author and stamp are the user to initially create the row and datetime it occurred. It should never be modified after creation.
/****** Object: Table [dbo].[BranchMstr] Script Date: 4/8/2019 5:00:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BranchMstr](
[BranchMstrKey] [uniqueidentifier] NOT NULL,
[Description] [varchar](1000) NULL,
[author] [varchar](50) NULL,
[created] [datetime] NOT NULL,
[userid] [varchar](50) NULL,
[stamp] [datetime] NULL,
CONSTRAINT [PK_BranchMstr] PRIMARY KEY CLUSTERED
(
[BranchMstrKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[BranchMstr] ADD CONSTRAINT [DF_BranchMstr_BranchGuid] DEFAULT (newid()) FOR [BranchMstrKey]
GO
ALTER TABLE [dbo].[BranchMstr] ADD CONSTRAINT [DF_BranchMstr_stamp] DEFAULT (getutcdate()) FOR [created]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'General information about this branch of questions.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BranchMstr', @level2type=N'COLUMN',@level2name=N'Description'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'user to originally create the record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BranchMstr', @level2type=N'COLUMN',@level2name=N'author'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date time stamp of creation' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BranchMstr', @level2type=N'COLUMN',@level2name=N'created'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'user to last modify the record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BranchMstr', @level2type=N'COLUMN',@level2name=N'userid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'datetime stamp record was last modified' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BranchMstr', @level2type=N'COLUMN',@level2name=N'stamp'
GO
/****** Object: Table [dbo].[Questions] Script Date: 4/8/2019 5:01:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Questions](
[QKey] [uniqueidentifier] NOT NULL,
[BranchMstrKey] [varchar](128) NULL,
[QOrder] [int] NULL,
[AKeyXref] [varchar](128) NULL,
[Question] [varchar](3000) NULL,
[Details] [varchar](500) NULL,
[StartDte] [date] NULL,
[EndDte] [date] NULL,
[Mandatory] [bit] NULL,
[Notes] [varchar](3000) NULL,
[author] [varchar](50) NULL,
[created] [datetime] NULL,
[userid] [varchar](50) NULL,
[stamp] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Questions] ADD CONSTRAINT [DF_Questions_QKey] DEFAULT (newid()) FOR [QKey]
GO
ALTER TABLE [dbo].[Questions] ADD CONSTRAINT [DF_Questions_QOrder] DEFAULT ((0)) FOR [QOrder]
GO
ALTER TABLE [dbo].[Questions] ADD CONSTRAINT [DF_Questions_Mandatory] DEFAULT ((0)) FOR [Mandatory]
GO
ALTER TABLE [dbo].[Questions] ADD CONSTRAINT [DF_Questions_stamp] DEFAULT (getutcdate()) FOR [created]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This field will be presented with the question; providing more explanation to the question.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Questions', @level2type=N'COLUMN',@level2name=N'Details'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'for internal use only. this column will never be displayed to survey-takers but only to administrators' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Questions', @level2type=N'COLUMN',@level2name=N'Notes'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'last user to modify the row' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Questions', @level2type=N'COLUMN',@level2name=N'userid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date time stamp last modified' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Questions', @level2type=N'COLUMN',@level2name=N'stamp'
GO
/****** Object: Table [dbo].[Answers] Script Date: 4/8/2019 5:01:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Answers](
[AKey] [uniqueidentifier] NOT NULL,
[BranchMstrKey] [uniqueidentifier] NULL,
[QKey] [uniqueidentifier] NOT NULL,
[Answer] [varchar](1000) NULL,
[PresentationOrder] [varchar](6) NULL,
[Details] [varchar](500) NULL,
[StartDte] [date] NULL,
[EndDte] [date] NULL,
[Notes] [varchar](3000) NULL,
[author] [varchar](50) NULL,
[created] [datetime] NOT NULL,
[userid] [varchar](50) NULL,
[stamp] [datetime] NULL,
CONSTRAINT [PK_Answers] PRIMARY KEY CLUSTERED
(
[AKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Answers] ADD CONSTRAINT [DF_Answers_BranchMstrKey] DEFAULT (newid()) FOR [BranchMstrKey]
GO
ALTER TABLE [dbo].[Answers] ADD CONSTRAINT [DF_Answers_stamp] DEFAULT (getutcdate()) FOR [created]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Defines the order answers should be presented. Any alpha or numeric can be used and can also be used for presentation on screen. Optional' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Answers', @level2type=N'COLUMN',@level2name=N'PresentationOrder'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Presented as sub-text to each answer for the end-user. Optional.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Answers', @level2type=N'COLUMN',@level2name=N'Details'
GO