SQL server:create Table SQL script

---Draw award observeh database design Function getspace lottery/*--Author:geovindu-date:20180427 to generate code for self-write. Depending on the metedata generated code with comments and relational tables, the table must have primary keys and comments create DATABASE lotterydrawinggouse lotterydrawinggo*/---items table awardprojectif EXISTS (SELECT * from sysobjects where id = object_id (N ' dbo. Awardproject ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) DROP table Awardproject gocreate table Awardproject (ProjectID I NT IDENTITY (max) PRIMARY KEY not Null,projectname NVARCHAR (a) Not NULL,--item name, showing annual projectyear VARCHAR not null,- -Annual name Projectdate DATETIME DEFAULT (GETDATE ())--creation date goif not EXISTS (SELECT * from:: Fn_listextendedproperty (N ' Ms_ Description ', n ' SCHEMA ', n ' dbo ', n ' TABLE ', n ' awardproject ', null,null)) EXEC sys.sp_addextendedproperty @name =n ' Ms_ Description ', @value =n ' prize items table ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' table ', @level1name =n ' Awardproject ', @level2type =null, @level2name =nullgo--column describes exec sys.sp_addextendedproperty @name =n ' ms_description ', @ Value=n ' item id ', @level0type =n ' SCHEMA ', @levEl0name=n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Awardproject ', @level2type =n ' COLUMN ', @level2name =n ' ProjectID ' Goexec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' project name, showing annual meeting ', @level0type =n ' SCHEMA ', @ Level0name=n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Awardproject ', @level2type =n ' COLUMN ', @level2name =n ' ProjectName ' goexec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' year name ', @level0type =n ' SCHEMA ', @ Level0name=n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Awardproject ', @level2type =n ' COLUMN ', @level2name =n ' Projectyear ' goexec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' created Date ', @level0type =n ' SCHEMA ', @ Level0name=n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Awardproject ', @level2type =n ' COLUMN ', @level2name =n ' Projectdate ' goselect * FROM dbo. awardprojectgo--Draw Employeeif EXISTS (select * from sysobjects where id = object_id (N ' dbo. Employee ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) DROP Table Employee Gocreate table employee (EmployeeId INT IDENTity (PRIMARY key not Null,employeeprojectid INT FOREIGN key REFERENCES awardproject (ProjectID),--external keys, items Idemployeeno varchar (+) NOT NULL,--worker number EmployeeName NVARCHAR (+) NOT NULL,--Employee name Employeemobile VARCHAR (a) NOT NULL,--phone number--SKYP Eemployeedate DATETIME DEFAULT (GETDATE ())) GO---table describes if not EXISTS (SELECT * from:: Fn_listextendedproperty (N ' Ms_ Description ', n ' SCHEMA ', n ' dbo ', n ' TABLE ', n ' ticketmanage ', null,null)) EXEC sys.sp_addextendedproperty @name =n ' Ms_ Description ', @value =n ' raffle ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' table ', @level1name =n ' Employee ', @level2type =null, @level2name =nullgo--column describes the exec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' employee ID ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Employee ', @level2type =n ' COLUMN ', @level2name =n ' EmployeeId ' goexec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' external keys, item ID ', @ Level0type=n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Employee ',@level2type =n ' COLUMN ', @level2name =n ' Employeeprojectid ' goexec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' employee number ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Employee ', @ Level2type=n ' COLUMN ', @level2name =n ' Employeeno ' goexec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' Employee name ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Employee ', @level2type =n ' COLUMN ', @level2name =n ' EmployeeName ' goexec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' phone number ', @ Level0type=n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Employee ', @level2type =n ' COLUMN ', @ Level2name=n ' employeemobile ' goexec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' Date ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Employee ', @level2type =n ' COLUMN ', @level2name =n ' Employeedate ' goselect * FROM dbo. employeego--Awards Table Awardtypeif EXISTS (SELECT * from sysobjects where id = object_id (N ' dbo. Awardtype ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) DROP table Awardtype gocreate table Awardtype (Awardtypeid INT IDEN Tity (PRIMARY key not Null,awardprojectid INT FOREIGN key REFERENCES awardproject (ProjectID),--external keys, items idawardname NVARCHAR (1) Not NULL,--prize name, such as: Principal prize, Class Awardpersonnumber int default, Awardverynumber int default (1),-- Number of people awardveryproduct INT DEFAULT (1)--each product quantity) GO---table describes if not EXISTS (SELECT * from:: Fn_listextendedproperty (N ' Ms_ Description ', n ' SCHEMA ', n ' dbo ', n ' TABLE ', n ' ticketmanage ', null,null)) EXEC sys.sp_addextendedproperty @name =n ' Ms_ Description ', @value =n ' Prize table ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' table ', @level1name =n ' Awardtype ', @level2type =null, @level2name =nullgo--column describes exec sys.sp_addextendedproperty @name =n ' ms_description ', @ Value=n ' Prize id ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Awardtype ', @ Level2type=n ' COLUMN ', @level2name =n ' Awardtypeid ' goexec sys.sp_addextendedproperty @name=n ' ms_description ', @value =n ' external keys, item ID ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Awardtype ', @level2type =n ' COLUMN ', @level2name =n ' Awardprojectid ' goexec sys.sp_addextendedproperty @name =n ' Ms_ Description ', @value =n ' award title, such as: Principal prize, Class Prize ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @ Level1name=n ' Awardtype ', @level2type =n ' COLUMN ', @level2name =n ' awardname ' goexec sys.sp_addextendedproperty @name =n ' Ms_description ', @value =n ' prize numbers ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Awardtype ', @level2type =n ' COLUMN ', @level2name =n ' awardpersonnumber ' goexec sys.sp_addextendedproperty @name =n ' Ms_ Description ', @value =n ' number of people per draw ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Awardtype ', @level2type =n ' COLUMN ', @level2name =n ' awardverynumber ' goexec sys.sp_addextendedproperty @name =n ' Ms_ Description ', @value =n ' prizes per person, @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' AwarDType ', @level2type =n ' COLUMN ', @level2name =n ' awardveryproduct ' goselect * FROM dbo. awardtypego--Prize Table Awardproductif EXISTS (SELECT * from sysobjects where id = object_id (N ' dbo. Awardproduct ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) DROP table awardproduct gocreate table awardproduct (ProductId I NT IDENTITY (PRIMARY) key not null,productawardtypeidintforeign key REFERENCES Awardtype (Awardtypeid),--external keys, Prize type Idproductname NVARCHAR ($) NOT NULL,--award name Productno VARCHAR () NULL,--Bonus number (easy to scan) Producttotal INT not null--bonus ( The total amount must match the prize number * Each prize quantity matches, the procedure must verify)) GO---table describes if not EXISTS (SELECT * from:: Fn_listextendedproperty (n ' ms_description ', n ' SCHEMA ') , n ' dbo ', n ' table ', n ' awardproduct ', null,null)) EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' prize table ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' awardproduct ', @level2type =null,@ level2name=nullgo--column describes the exec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' prize id ', @level0type =n ' SCHEMA ', @level0name=n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' awardproduct ', @level2type =n ' COLUMN ', @level2name =n ' ProductId ' goexec Sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' external keys, prize type ID ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ' , @level1type =n ' TABLE ', @level1name =n ' awardproduct ', @level2type =n ' COLUMN ', @level2name =n ' Productawardtypeid ' Goexec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' prize name ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' awardproduct ', @level2type =n ' COLUMN ', @level2name =n ' ProductName ' goexec Sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' prize number ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @ Level1type=n ' TABLE ', @level1name =n ' awardproduct ', @level2type =n ' COLUMN ', @level2name =n ' Productno ' goexec sys.sp_ Addextendedproperty @name =n ' ms_description ', @value =n ' prize number ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' awardproduct ', @level2type =n ' COLUMN ', @level2name =n ' producttotal ' goselect * FROM dbo.awardproductgo--Prize Picture Table if EXISTS (SELECT * from sysobjects where id = object_id (N ' dbo. Awardproductimage ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) DROP table awardproductimage gocreate table Awardproducti Mage (Productimageid INT IDENTITY (PRIMARY) key not null,productimageproductidintforeign key REFERENCES awardproduct ( PRODUCTID),--external key, product Idproductimagebyte image--product picture) GO---table describes if not EXISTS (SELECT * from:: Fn_listextendedproperty (N ' Ms_ Description ', n ' SCHEMA ', n ' dbo ', n ' TABLE ', n ' awardproductimage ', null,null)) EXEC sys.sp_addextendedproperty @name =n ' Ms_description ', @value =n ' prize picture table ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' table ', @level1name =n ' Awardproductimage ', @level2type =null, @level2name =nullgo--column describes exec sys.sp_addextendedproperty @name =n ' Ms_ Description ', @value =n ' graphics id ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Awardproductimage ', @level2type =n ' COLUMN ', @level2name =n ' Productimageid ' goexec sys.sp_addextendedproperty @name =n ' Ms_descrIption ', @value =n ' external key, Product ID ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Awardproductimage ', @level2type =n ' COLUMN ', @level2name =n ' Productimageproductid ' goexec sys.sp_addextendedproperty @ Name=n ' ms_description ', @value =n ' product graphics ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @ Level1name=n ' Awardproductimage ', @level2type =n ' COLUMN ', @level2name =n ' productimagebyte ' goselect * FROM dbo. awardproductimagego--Winners List Luckywinner---award confirmation if EXISTS (SELECT * from sysobjects where id = object_id (N ' dbo. Luckywinner ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) DROP table Luckywinner gocreate table Luckywinner (Winnerid INT I Dentity (PRIMARY key not Null,winneremployeeid INT FOREIGN key REFERENCES Employee (EmployeeId),--external keys, Staff idwinnerawardtypeidintforeign KEY REFERENCES awardtype (awardtypeid),--external keys, prize type Idwinnerisok BIT DEFAULT (0),-- Whether to sign an acknowledgment Winnersinger NVARCHAR () NULL,--the signature (or agent) winnerdate datetime null--Confirmation date) GO---table describes if not EXISTS (SELECT * FROM:: Fn_Listextendedproperty (n ' ms_description ', n ' SCHEMA ', n ' dbo ', n ' TABLE ', n ' Luckywinner ', null,null)) EXEC sys.sp_ Addextendedproperty @name =n ' ms_description ', @value =n ' winner List ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @ Level1type=n ' TABLE ', @level1name =n ' Luckywinner ', @level2type =null, @level2name =nullgo--column describes exec sys.sp_ Addextendedproperty @name =n ' ms_description ', @value =n ' winner ID ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @ Level1type=n ' TABLE ', @level1name =n ' Luckywinner ', @level2type =n ' COLUMN ', @level2name =n ' Winnerid ' goexec sys.sp_ Addextendedproperty @name =n ' ms_description ', @value =n ' external key, employee ID ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @ Level1type=n ' TABLE ', @level1name =n ' Luckywinner ', @level2type =n ' COLUMN ', @level2name =n ' Winneremployeeid ' goexec Sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' external keys, prize type ID ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ' , @level1type =n ' TABLE ', @level1name =n ' Luckywinner ', @level2type =n ' COLUMN ', @level2name =n ' Winnerawardtypeid ' goexec Sys.sp_addextendedproperty @name=n ' ms_description ', @value =n ' whether to sign the acknowledgment ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name = N ' Luckywinner ', @level2type =n ' COLUMN ', @level2name =n ' Winnerisok ' goexec sys.sp_addextendedproperty @name =n ' Ms_ Description ', @value =n ' autograph (or agent) ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Luckywinner ', @level2type =n ' COLUMN ', @level2name =n ' Winnersinger ' goexec sys.sp_addextendedproperty @name =n ' Ms_ Description ', @value =n ' confirmation of date ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Luckywinner ', @level2type =n ' COLUMN ', @level2name =n ' winnerdate ' goselect * FROM dbo. luckywinnergo--harvesting the winner SMS, Skype information winnersmsif EXISTS (SELECT * from sysobjects where id = object_id (N ' dbo. Winnersms ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) DROP table winnersms gocreate table winnersms (SmsId INT IDENTITY (1 , 1) PRIMARY key not Null,smsemployeeid INT FOREIGN key REFERENCES Employee (EmployeeId),--external keys, Staff idsmscontent NVARCHAR (2000 ) not NULL,--SMS content SMSDate DATETIME DEFAULT (GETDATE ())--send time) GO---table describes if not EXISTS (SELECT * from:: Fn_listextendedproperty (N ' ms_description ' N ' SCHEMA ', n ' dbo ', n ' TABLE ', n ' winnersms ', null,null)) EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @ Value=n ' Harvesting prize message form, @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' table ', @level1name =n ' winnersms ', @ Level2type=null, @level2name =nullgo--column describes the exec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' SMS ID ', @ Level0type=n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' winnersms ', @level2type =n ' COLUMN ', @ Level2name=n ' SmsId ' goexec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' external key, employee ID ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' winnersms ', @level2type =n ' COLUMN ', @level2name =n ' Smsemployeeid ' goexec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' message contents ', @level0type =n ' SCHEMA ', @ Level0name=n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' winnersms ', @level2type =n ' COLUMN ', @level2naMe=n ' smscontent ' goexec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' send Time ', @level0type =n ' SCHEMA ', @ Level0name=n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' winnersms ', @level2type =n ' COLUMN ', @level2name =n ' smsdate ' Goselect * FROM dbo. winnersmsgo--window Background Graph if EXISTS (select * from sysobjects where id = object_id (N ' dbo. Formbackground ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) DROP table Formbackground gocreate table Formbackground (back Groundid INT IDENTITY (PRIMARY) KEY not null,backgroundimage image,backgroundfmname NVARCHAR ($)) GOif not EXISTS (SEL ECT * FROM:: Fn_listextendedproperty (n ' ms_description ', n ' SCHEMA ', n ' dbo ', n ' TABLE ', n ' formbackground ', null,null)) EXEC sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' window background graph ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' Formbackground ', @level2type =null, @level2name =nullgo--column describes exec sys.sp_ Addextendedproperty @name =n ' ms_description ', @value =n ' ID ', @level0type =n ' SCHEMA ', @level0name =n 'dbo ', @level1type =n ' TABLE ', @level1name =n ' Formbackground ', @level2type =n ' COLUMN ', @level2name =n ' Backgroundid ' Goexec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' background map ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ' , @level1type =n ' TABLE ', @level1name =n ' Formbackground ', @level2type =n ' COLUMN ', @level2name =n ' backgroundimage ' goexec Sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' window name ', @level0type =n ' SCHEMA ', @level0name =n ' dbo ', @ Level1type=n ' TABLE ', @level1name =n ' Formbackground ', @level2type =n ' COLUMN ', @level2name =n ' Backgroundfmname ' Goselect * FROM dbo. Formbackground

　　

SQL server:create Table SQL script