CREATE TYPE [dbo].[StoDriveId] from [int]
CREATE TYPE [dbo].[Drive] from char(1)
CREATE TYPE [dbo].[Name] from [varchar](128)
CREATE TYPE [dbo].[FileSize] from [decimal](18, 2)
CREATE TYPE [dbo].[StoHistoryHeaderId] from [int]
CREATE TYPE [dbo].[StoHistoryDriveDetailId] from [int]
CREATE TYPE [dbo].[StoDbId] from [int]
CREATE TYPE [dbo].[DBName] FROM [varchar](128)
CREATE TYPE [dbo].[StoHistoryDbDetailId] from [int]
CREATE TYPE [dbo].[StoDriveDbMapId] from [int]
CREATE TYPE [dbo].[CtsJobId] from [int]
CREATE TYPE [dbo].[CtsJobHistoryHeaderId] from [int]
CREATE TYPE [dbo].[CtsJobHistoryDetailId] from [int]
CREATE TYPE [dbo].[RunDuration] from [int]
CREATE TYPE [dbo].[RunStatus] from [int]
CREATE TYPE [dbo].[SettingEmailId] from [int]
CREATE TYPE [dbo].[EmailCode] FROM [varchar](20)
CREATE TYPE [dbo].[EmailProfile] FROM [varchar](50)
CREATE TYPE [dbo].[EmailFrom] FROM [varchar](max)
CREATE TYPE [dbo].[EmailTo] FROM [varchar](max)
CREATE TYPE [dbo].[EmailCc] FROM [varchar](max)
CREATE TYPE [dbo].[EmailFreqType] FROM [varchar](10) --Daily, Weekly, Monthly
CREATE TYPE [dbo].[EmailFreqInterval] FROM [varchar](255) --comma seperated integer
CREATE TYPE [dbo].[EmailLogId] from [int]
CREATE TYPE [dbo].[EmailSubject] FROM [varchar](max)
CREATE TYPE [dbo].[EmailBody] FROM [varchar](max)
GO
--------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[StoDrive](
[StoDriveId] [dbo].[StoDriveId] IDENTITY(1,1) NOT NULL,
[Drive] [dbo].[Drive] NOT NULL,
[DriveName] [dbo].[Name] NULL,
[TotalSpaceInMB] [dbo].[FileSize] NOT NULL,
[IsActive] bit NOT NULL DEFAULT (1),
[CreateDate] datetime DEFAULT GETDATE(),
CONSTRAINT PK_StoDrive PRIMARY KEY CLUSTERED
(
[StoDriveId] 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
--------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[StoHistoryHeader](
[StoHistoryHeaderId] [dbo].[StoHistoryHeaderId] IDENTITY(1,1) NOT NULL,
[RunDate] datetime DEFAULT GETDATE(),
CONSTRAINT PK_StoHistoryHeader PRIMARY KEY CLUSTERED
(
[StoHistoryHeaderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[StoHistoryDriveDetail](
[StoHistoryDriveDetailId] [dbo].[StoHistoryDriveDetailId] IDENTITY(1,1) NOT NULL,
[StoHistoryHeaderId] [dbo].[StoHistoryHeaderId] NOT NULL,
[StoDriveId] [dbo].[StoDriveId] NOT NULL,
[FreeSpaceInMB] [dbo].[FileSize] NOT NULL,
CONSTRAINT PK_StoHistoryDriveDetail PRIMARY KEY CLUSTERED
(
[StoHistoryDriveDetailId] 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].[StoHistoryDriveDetail] WITH CHECK ADD CONSTRAINT [FK_StoDriveHistory_StoHistoryHeaderId] FOREIGN KEY([StoHistoryHeaderId])
REFERENCES [dbo].[StoHistoryHeader] ([StoHistoryHeaderId])
GO
CREATE NONCLUSTERED INDEX [IX_StoDriveHistory_StoHistoryHeaderId] ON [dbo].[StoHistoryDriveDetail]
(
[StoHistoryHeaderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StoHistoryDriveDetail] WITH CHECK ADD CONSTRAINT [FK_StoDriveHistory_StoDriveId] FOREIGN KEY([StoDriveId])
REFERENCES [dbo].[StoDrive] ([StoDriveId])
GO
CREATE NONCLUSTERED INDEX [IX_StoDriveHistory_StoDriveId] ON [dbo].[StoHistoryDriveDetail]
(
[StoDriveId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
--------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[StoDb](
[StoDbId] [dbo].[StoDbId] IDENTITY(1,1) NOT NULL,
[DBName] [dbo].[DBName] NULL,
[IsActive] bit NOT NULL DEFAULT (1),
[CreateDate] datetime DEFAULT GETDATE(),
CONSTRAINT PK_StoDb PRIMARY KEY CLUSTERED
(
[StoDbId] 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
--------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[StoDriveDbMap](
[StoDriveDbMapId] [dbo].[StoDriveDbMapId] IDENTITY(1,1) NOT NULL,
[StoDriveId] [dbo].[StoDriveId] NOT NULL,
[StoDbId] [dbo].[StoDbId] NOT NULL,
[IsActive] bit NOT NULL DEFAULT (1),
[CreateDate] datetime DEFAULT GETDATE(),
CONSTRAINT PK_StoDriveDbMap PRIMARY KEY CLUSTERED
(
[StoDriveDbMapId] 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].[StoDriveDbMap] WITH CHECK ADD CONSTRAINT [FK_StoDriveDbMap_StoDriveId] FOREIGN KEY([StoDriveId])
REFERENCES [dbo].[StoDrive] ([StoDriveId])
GO
CREATE NONCLUSTERED INDEX [IX_StoDriveDbMap_StoDriveId] ON [dbo].[StoDriveDbMap]
(
[StoDriveId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StoDriveDbMap] WITH CHECK ADD CONSTRAINT [FK_StoDriveDbMap_StoDbId] FOREIGN KEY([StoDbId])
REFERENCES [dbo].[StoDb] ([StoDbId])
GO
CREATE NONCLUSTERED INDEX [IX_StoDriveDbMap_StoDbId] ON [dbo].[StoDriveDbMap]
(
[StoDbId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
--------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[StoHistoryDbDetail](
[StoHistoryDbDetailId] [dbo].[StoHistoryDbDetailId] IDENTITY(1,1) NOT NULL,
[StoHistoryHeaderId] [dbo].[StoHistoryHeaderId] NOT NULL,
[StoDbId] [dbo].[StoDbId] NOT NULL,
[DBSizeMB] [dbo].[FileSize] NOT NULL,
[LogSizeMB] [dbo].[FileSize] NOT NULL,
[UnallocatedSpaceMB] [dbo].[FileSize] NOT NULL,
[ReservedKB] [dbo].[FileSize] NOT NULL,
[DataKB] [dbo].[FileSize] NOT NULL,
[IndexSizeKB] [dbo].[FileSize] NOT NULL,
[UnusedKB] [dbo].[FileSize] NOT NULL,
CONSTRAINT PK_StoHistoryDbDetail PRIMARY KEY CLUSTERED
(
[StoHistoryDbDetailId] 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].[StoHistoryDbDetail] WITH CHECK ADD CONSTRAINT [FK_StoHistoryDbDetail_StoHistoryHeaderId] FOREIGN KEY([StoHistoryHeaderId])
REFERENCES [dbo].[StoHistoryHeader] ([StoHistoryHeaderId])
GO
CREATE NONCLUSTERED INDEX [IX_StoHistoryDbDetail_StoHistoryHeaderId] ON [dbo].[StoHistoryDbDetail]
(
[StoHistoryHeaderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StoHistoryDbDetail] WITH CHECK ADD CONSTRAINT [FK_StoHistoryDbDetail_StoDbId] FOREIGN KEY([StoDbId])
REFERENCES [dbo].[StoDb] ([StoDbId])
GO
CREATE NONCLUSTERED INDEX [IX_StoHistoryDbDetail_StoDbId] ON [dbo].[StoDb]
(
[StoDbId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
--------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[CtsJob](
[CtsJobId] [dbo].[CtsJobId] IDENTITY(1,1) NOT NULL,
[JobId] uniqueidentifier NOT NULL,
[JobName] sysname NULL,
[IsActive] bit NOT NULL DEFAULT (1),
[ShowInReport] bit NOT NULL DEFAULT(1),
[CreateDate] datetime DEFAULT GETDATE(),
CONSTRAINT PK_CtsJob PRIMARY KEY CLUSTERED
(
[CtsJobId] 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
CREATE UNIQUE INDEX AK_CtsJob_JobId
ON dbo.CtsJob (JobId);
GO
--------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[CtsJobHistoryHeader](
[CtsJobHistoryHeaderId] [dbo].[CtsJobHistoryHeaderId] IDENTITY(1,1) NOT NULL,
[RunDate] date NOT NULL,
[CreateDate] datetime DEFAULT GETDATE(),
CONSTRAINT PK_CtsJobHistoryHeader PRIMARY KEY CLUSTERED
(
[CtsJobHistoryHeaderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[CtsJobHistoryDetail](
[CtsJobHistoryDetailId] [dbo].[CtsJobHistoryDetailId] IDENTITY(1,1) NOT NULL,
[CtsJobHistoryHeaderId] [dbo].[CtsJobHistoryHeaderId] NOT NULL,
[CtsJobId] [dbo].[CtsJobId] NOT NULL,
[RunTime] time NULL,
[RunDuration] [dbo].[RunDuration] NULL,
[RunStatus] [dbo].[RunStatus] NULL,
[IsScheduled] bit,
CONSTRAINT PK_CtsJobHistoryDetail PRIMARY KEY CLUSTERED
(
[CtsJobHistoryDetailId] 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].[CtsJobHistoryDetail] WITH CHECK ADD CONSTRAINT [FK_CtsJobHistoryDetail_CtsJobHistoryHeaderId] FOREIGN KEY([CtsJobHistoryHeaderId])
REFERENCES [dbo].[CtsJobHistoryHeader] ([CtsJobHistoryHeaderId])
GO
CREATE NONCLUSTERED INDEX [IX_CtsJobHistoryDetail_CtsJobHistoryHeaderId] ON [dbo].[CtsJobHistoryDetail]
(
[CtsJobHistoryHeaderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CtsJobHistoryDetail] WITH CHECK ADD CONSTRAINT [FK_CtsJobHistoryDetail_CtsJobId] FOREIGN KEY([CtsJobId])
REFERENCES [dbo].[CtsJob] ([CtsJobId])
GO
CREATE NONCLUSTERED INDEX [IX_CtsJobHistoryDetail_CtsJobId] ON [dbo].[CtsJobHistoryDetail]
(
[CtsJobId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
--------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[SettingEmail](
[SettingEmailId] [dbo].[SettingEmailId] IDENTITY(1,1) NOT NULL,
[EmailCode] [dbo].[EmailCode] NOT NULL,
[EmailProfile] [dbo].[EmailProfile] NULL,
[EmailFrom] [dbo].[EmailFrom] NULL,
[EmailTo] [dbo].[EmailTo] NULL,
[EmailCc] [dbo].[EmailCc] NULL,
[EmailFreqType] [dbo].[EmailFreqType] NULL,
[EmailFreqInterval] [dbo].[EmailFreqInterval] NULL,
[IsActive] bit NOT NULL DEFAULT (1),
[CreateDate] datetime DEFAULT GETDATE(),
CONSTRAINT PK_SettingEmail PRIMARY KEY CLUSTERED
(
[SettingEmailId] 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
CREATE UNIQUE INDEX AK_SettingEmail_EmailCode
ON dbo.SettingEmail (EmailCode);
GO
--------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[EmailLog](
[EmailLogId] [dbo].[EmailLogId] IDENTITY(1,1) NOT NULL,
[EmailCode] [dbo].[EmailCode] NULL,
[EmailProfile] [dbo].[EmailProfile] NULL,
[EmailFrom] [dbo].[EmailFrom] NULL,
[EmailTo] [dbo].[EmailTo] NULL,
[EmailCc] [dbo].[EmailCc] NULL,
[EmailSubject] [dbo].[EmailSubject] NULL,
[EmailBody] [dbo].[EmailBody] NULL,
[CreateDate] datetime DEFAULT GETDATE(),
CONSTRAINT PK_EmailLog PRIMARY KEY CLUSTERED
(
[EmailLogId] 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
--------------------------------------------------------------------------------------------------------------
/*
Insert into SettingEmail (EmailCode, EmailProfile, EmailFrom, EmailTo, EmailCC, EmailFreqType, EmailFreqInterval)
values ('Job_Report', 'Default', 'youxing@gmail.com', 'youxing@gmail.com', 'youdexing@gmail.com', 'Weekly', '1,3,5')
*//
No comments:
Post a Comment