SQL Server: create table sql script

標籤：table   orm   product   msi   rom   def   tab   eve   back   

---搖獎observeh資料庫設計  Function getSpace  lottery/*-- Author:geovindu 塗聚文-- Date： 20180427 為了自寫產生代碼。根據Metedata 產生有注釋和關係表的代碼，表必須要主鍵和注釋CREATE DATABASE LotteryDrawingGOUSE LotteryDrawingGO*/---獎項項目表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 INT IDENTITY(1,1) PRIMARY KEY NOT NULL,ProjectName NVARCHAR(300) NOT NULL,--項目名稱，顯示年度年會上ProjectYear VARCHAR(20) NOT NULL,--年度名稱ProjectDate DATETIME DEFAULT(GETDATE())--創建日期)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‘獎項項目表‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘AwardProject‘, @level2type=NULL,@level2name=NULLGO--列描述EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘項目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‘項目名稱，顯示年度年會上‘ , @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‘年度名稱‘ , @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‘創建日期‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘AwardProject‘, @level2type=N‘COLUMN‘,@level2name=N‘ProjectDate‘GOSELECT * FROM dbo.AwardProjectGO--抽獎員工表 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(1,1) PRIMARY KEY NOT NULL,EmployeeProjectId INT FOREIGN KEY REFERENCES AwardProject(ProjectId),--外鍵，項目IDEmployeeNO VARCHAR(30) NOT NULL,    --員工編號EmployeeName NVARCHAR(100) NOT NULL, --員工姓名EmployeeMobile VARCHAR(20) NOT NULL,--手機號碼--skypeEmployeeDate DATETIME DEFAULT(GETDATE()))GO---表描述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‘抽獎員工表‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Employee‘, @level2type=NULL,@level2name=NULLGO--列描述EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘員工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‘外鍵，項目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‘員工編號‘ , @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‘員工姓名‘ , @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‘手機號碼‘ , @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‘日期‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Employee‘, @level2type=N‘COLUMN‘,@level2name=N‘EmployeeDate‘GOSELECT * FROM dbo.EmployeeGO--獎項表 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 IDENTITY(1,1) PRIMARY KEY NOT NULL,AwardProjectId INT FOREIGN KEY REFERENCES AwardProject(ProjectId),--外鍵，項目IDAwardName NVARCHAR(100) NOT NULL,--獎項名稱，如：特等獎，一等獎AwardPersonNumber INT DEFAULT(1),--獎項人數AwardVeryNumber INT DEFAULT(1),--每次抽的人數AwardVeryProduct INT DEFAULT(1)--獎項每人產品數量)GO---表描述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‘獎項表 ‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘AwardType‘, @level2type=NULL,@level2name=NULLGO--列描述EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘獎項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‘外鍵，項目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‘獎項名稱，如：特等獎，一等獎‘ , @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‘獎項人數‘ , @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‘每次抽的人數‘ , @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‘獎項每人產品數量‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘AwardType‘, @level2type=N‘COLUMN‘,@level2name=N‘AwardVeryProduct‘GOSELECT * FROM dbo.AwardTypeGO--獎品表 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 INT IDENTITY(1,1) PRIMARY KEY NOT NULL,ProductAwardTypeIdINTFOREIGN KEY REFERENCES AwardType(AwardTypeId),--外鍵，獎項類型IDProductName NVARCHAR(200) NOT NULL,--獎品名稱ProductNo VARCHAR(50) NULL,--獎品編號(便於掃碼）ProductTotal INT NOT NULL--獎品數量 (總數量要和獎品人數*每個獎項數量相符，程式要驗證）)GO---表描述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‘獎品表‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘AwardProduct‘, @level2type=NULL,@level2name=NULLGO--列描述EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘獎品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‘外鍵，獎項類型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‘獎品名稱‘ , @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‘獎品編號‘ , @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‘獎品數量‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘AwardProduct‘, @level2type=N‘COLUMN‘,@level2name=N‘ProductTotal‘GOSELECT * FROM dbo.AwardProductGO--獎品圖片表IF EXISTS (select * from sysobjects where id = object_id(N‘dbo.AwardProductImage‘) and OBJECTPROPERTY(id, N‘IsUserTable‘) = 1) DROP TABLE AwardProductImage GOCREATE TABLE AwardProductImage(ProductImageId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,ProductImageProductIdINTFOREIGN KEY REFERENCES AwardProduct(ProductId),--外鍵，產品IDProductImageByte IMAGE--產品圖片)GO---表描述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‘獎品圖片表‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘AwardProductImage‘, @level2type=NULL,@level2name=NULLGO--列描述EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘圖片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‘外鍵，產品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‘產品圖片‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘AwardProductImage‘, @level2type=N‘COLUMN‘,@level2name=N‘ProductImageByte‘GOSELECT * FROM dbo.AwardProductImageGO--獲獎者列表 LuckyWinner  ---獎品確認收簽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 IDENTITY(1,1) PRIMARY KEY NOT NULL,WinnerEmployeeId INT FOREIGN KEY REFERENCES Employee(EmployeeId),--外鍵，員工IDWinnerAwardTypeIdINTFOREIGN KEY REFERENCES AwardType(AwardTypeId),--外鍵，獎項類型IDWinnerIsOk BIT DEFAULT(0),--是否簽收確認WinnerSinger NVARCHAR(50) NULL,--簽收人（或代理人）WinnerDate datetime NULL--確認簽收日期)GO---表描述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‘獲獎者列表‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘LuckyWinner‘, @level2type=NULL,@level2name=NULLGO--列描述EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘獲獎者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‘外鍵，員工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‘外鍵，獎項類型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‘是否簽收確認‘ , @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‘簽收人（或代理人）‘ , @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‘確認簽收日期‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘LuckyWinner‘, @level2type=N‘COLUMN‘,@level2name=N‘WinnerDate‘GOSELECT * FROM dbo.LuckyWinnerGO--所穫獎者發簡訊，SKYPE資訊等 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),--外鍵，員工IDSmsContent NVARCHAR(2000) NOT NULL,--簡訊內容SmsDate DATETIME DEFAULT(GETDATE())--發送時間)GO---表描述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‘所穫獎者發簡訊表‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘WinnerSms‘, @level2type=NULL,@level2name=NULLGO--列描述EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘簡訊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‘外鍵，員工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‘簡訊內容‘ , @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‘發送時間‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘WinnerSms‘, @level2type=N‘COLUMN‘,@level2name=N‘SmsDate‘GOSELECT * FROM dbo.WinnerSmsGO--視窗背景圖IF EXISTS (select * from sysobjects where id = object_id(N‘dbo.FormBackground‘) and OBJECTPROPERTY(id, N‘IsUserTable‘) = 1) DROP TABLE FormBackground GOCREATE TABLE FormBackground(BackgroundId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,BackgroundImage IMAGE,BackgroundFmName NVARCHAR(200))GOIF NOT EXISTS (SELECT * 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‘視窗背景圖表‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘FormBackground‘, @level2type=NULL,@level2name=NULLGO--列描述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‘背景圖‘ , @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‘視窗名稱‘ , @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