SQL statements create databases and tables

--Delete Database
Drop database Archivedev;

--Create an archive database
CREATE DATABASE Archivedev;

Use Archivedev;
GO

--1, create an archive plan execution table
CREATE TABLE Arch_collectdataexecschedule
(
ID INT not NULL,
Archcollectruleid INT not NULL,--Archive rule ID
Archparameterid INT not NULL,--Archive parameter ID
Scheduledate DATETIME not NULL,--Scheduled execution time
Exectime DATETIME not NULL,--Execution time
Autoormanual CHAR (4) Not NULL,--manual/Automatic
Schedulestatus CHAR (4),--plan status
Createemp INT,--Create person
CreateDate DATETIME,--creation time
Updatedate DATETIME--Update time
)
GO

--2, creating an archive Execution rules table
CREATE TABLE Arch_collectdatarule
(
ID INT not NULL,
Archparameterid INT not NULL,--archive parameter ID
Dateruleid INT not NULL,--time rule ID
Datacollectproc varchar,--Each type of archive data stored procedure name, with the parameters: Start time, end time, archive type, organization ID
Createemp INT,--Creator
CreateDate datetime,--Creation time
Updatedate datetime--Update Time
)
GO

--3, creating an archive Entry table
CREATE TABLE Arch_items
(
ID INT not NULL,
Archcollectscheduleid INT not NULL,--archive plan ID
Arch_businessfiesid INT not NULL,--archive Relationship ID
Archparameterid INT not NULL,--archive Relationship ID
Execstatus INT,--Archive execution status, 0: Pending Archive 1: Enter Intermediate Library 2: Archive complete
Scheduledate datetime,--Schedule Archive Date
Exectime DATETIME,--Actual archive time
oa_id int--file system library corresponding field
)
GO

--4, creating an Archive document instance table
CREATE TABLE Arch_itemfiles
(
ID INT not NULL,
Arch_filename VARCHAR ($),--Archive file name
URL VARCHAR ($),--document URL address
Documentpath VARCHAR ($),--file storage path
Archbusinessid INT,--document Business ID
Createemp INT,--Creator
CreateDate datetime,--Creation time
updatedate datetime,--Update Time
)
GO

--5, creating an Archive document instance table
CREATE TABLE arch_businessfies
(
ID INT not NULL,
Architemid INT not NULL,--archive Entry ID
Itemsourceid INT,--corresponding to the original system ID
DocumentID INT,--document primary Key ID
DocumentType CHAR (4),--document type
DocumentSource VARCHAR,--Document Source
Status int--file generation state
)
GO

--6, building a data dictionary table
CREATE TABLE arch_dict
(
ID INT not NULL,
Dtype CHAR (2) not NULL,--type
Dkey CHAR (4) Not NULL,--key
Dvalue CHAR (4) not NULL,--value
Sorting Sn INT,--
IsValid TINYINT,--is effective
Descrip NVARCHAR (200)--Dictionary description information
)
GO

--7, set up the archive parameter table
CREATE TABLE Arch_parameter
(
ID INT not NULL,
Archtype CHAR (4) Not NULL,--archive type
Begindate datetime,--Archive Start date
EndDate datetime,--Archive End Date
OrgID int--Archive Agency ID
)
GO

--8, set up archive time rule table
CREATE TABLE Arch_daterule
(
ID INT not NULL,
Dateruletype CHAR (4),--time rule type
BeginTime INT,--Archive start time
EndTime int--Archive Stop Time
)
GO

SQL statements create databases and tables