Summary Table
Categories |
Total Count |
PII |
0 |
URL |
0 |
DNS |
0 |
EKL |
0 |
IP |
0 |
PORT |
0 |
VsID |
0 |
CF |
0 |
AI |
0 |
VPD |
0 |
PL |
0 |
Other |
0 |
File Content
?CREATE TABLE dbo.MenuItems
(
--fields
MenuItemID SMALLINT IDENTITY(1,1) NOT NULL, --EPRS assigned (PK)
ParentMenuItemID SMALLINT,
MenuItemTypeID TINYINT NOT NULL,
SensitivityLevelID TINYINT NOT NULL,
CreatedByUserID INT NULL,
UpdatedByUserID INT NULL,
DateCreated DATETIME2 (7) NULL,
DateUpdated DATETIME2 (7) NULL,
MenuItemName VARCHAR(100) NOT NULL,
DisplayName VARCHAR(200) NOT NULL,
Area VARCHAR(200) NULL,
ControllerName VARCHAR(200) NULL,
ActionName VARCHAR(200) NULL,
UrlName VARCHAR(200) NULL,
QueryParameters VARCHAR(200) NULL,
Synopsis VARCHAR(2000),
AllowedRoles VARCHAR(25),
AssociatedModules VARCHAR(50),
SortOrder SMALLINT NOT NULL,
DateInactive DATETIME2(7),
Inactive BIT NOT NULL,
IsParent BIT NOT NULL,
IsArea BIT NOT NULL,
--primary key
CONSTRAINT PK_MenuItems PRIMARY KEY CLUSTERED
(
MenuItemID ASC
)
--primary key
)
ON DefinitionData
--when using clustered columnstore index, can't set data compression to PAGE
--WITH (DATA_COMPRESSION = PAGE) --less than 10,000 rows, don't need compression
GO
--foreign keys
ALTER TABLE dbo.MenuItems
ADD CONSTRAINT FK_MenuItems_ParentMenuItemID
FOREIGN KEY (ParentMenuItemID)
REFERENCES dbo.MenuItems (MenuItemID)
GO
ALTER TABLE dbo.MenuItems
ADD CONSTRAINT FK_MenuItems_MenuItemTypes
FOREIGN KEY (MenuItemTypeID)
REFERENCES dbo.MenuItemTypes (MenuItemTypeID)
GO
ALTER TABLE dbo.MenuItems
ADD CONSTRAINT FK_MenuItems_SensitivityLevels
FOREIGN KEY (SensitivityLevelID)
REFERENCES seclyr.SensitivityLevels (SensitivityLevelID)
GO
ALTER TABLE dbo.MenuItems
ADD CONSTRAINT [FK_MenuItems_CreatedByUserID]
FOREIGN KEY (CreatedByUserID)
REFERENCES seclyr.Users (UserID)
GO
ALTER TABLE dbo.MenuItems
ADD CONSTRAINT [FK_MenuItems_UpdatedByUserID]
FOREIGN KEY (UpdatedByUserID)
REFERENCES seclyr.Users (UserID)
GO
--indexes
CREATE NONCLUSTERED INDEX IX_MenuItems_ParentMenuItemID ON dbo.MenuItems
(
ParentMenuItemID ASC
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 90, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
DATA_COMPRESSION = NONE --don't need, less than 10,000 rows
)
ON DefinitionIndex
GO
CREATE NONCLUSTERED INDEX IX_MenuItems_MenuItemTypeID ON dbo.MenuItems
(
MenuItemTypeID ASC
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 90, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
DATA_COMPRESSION = NONE --don't need, less than 10,000 rows
)
ON DefinitionIndex
GO
--constraints
ALTER TABLE dbo.MenuItems
ADD CONSTRAINT DF_MenuItems_Inactive
DEFAULT 0
FOR Inactive
GO
ALTER TABLE dbo.MenuItems
ADD CONSTRAINT DF_MenuItems_IsParent
DEFAULT 0
FOR IsParent
GO
ALTER TABLE dbo.MenuItems
ADD CONSTRAINT DF_MenuItems_IsArea
DEFAULT 0
FOR IsArea
GO
--Metadata descriptions for each field
EXECUTE sp_addextendedproperty
N'MS_Description', N'Primary Key, unique to EPRS',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'MenuItemID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, is the parent menu item for this menu item, self-referencing key that relates to MenuItems table',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'ParentMenuItemID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Menu Item Types table',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'MenuItemTypeID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Sensitivity Levels table',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'SensitivityLevelID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Users table, User id who created the record',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'CreatedByUserID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Users table, User id who last updated the record',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'UpdatedByUserID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date record was created',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'DateCreated'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date record was last updated',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'DateUpdated'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Name for the Menu Item',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'MenuItemName'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Display name for the Menu Item',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'DisplayName'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Name for the area where the menu item may be used',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'Area'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Identifies the name of the controller class that should be invoked',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'ControllerName'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Identifies the name of the action method that should be invoked',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'ActionName'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'URL path for the MenuItem',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'UrlName'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Identifies the query parameters that should be used, separated by a pipe |',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'QueryParameters'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Description of the MenuItem',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'Synopsis'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Identifies what role(s) are permitted to see the report',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'AllowedRoles'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Identifies what module(s) are associated with see the report',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'AssociatedModules'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Order in which the value should show, if not alphabetical',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'SortOrder'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date record was inactivated',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'DateInactive'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Identifies if the record is inactive (1=true / 0=false)',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'Inactive'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Identifies if the MenuItem is a parent item (1=true / 0=false)',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'IsParent'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Identifies if the MenuItem is an area item (1=true / 0=false)',
N'SCHEMA', N'dbo',
N'TABLE', N'MenuItems',
N'COLUMN', N'IsArea'
GO