欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL Server: create table sql script

程序员文章站 2022-05-09 08:27:09
---摇奖observeh数据库设计 Function getSpace lottery /* -- Author:geovindu 涂聚文 -- Date: 20180427 为了自写生成代码。根据Metedata 生成有注释和关系表的代码,表必须要主键和注释 CREATE DATABASE Lo... ......
---摇奖observeh数据库设计  Function getSpace  lottery
/*
-- Author:geovindu 涂聚文
-- Date: 20180427 为了自写生成代码。根据Metedata 生成有注释和关系表的代码,表必须要主键和注释
CREATE DATABASE LotteryDrawing
GO

USE LotteryDrawing
GO
*/
---獎項項目表AwardProject
IF EXISTS (select * from sysobjects where id = object_id(N'dbo.AwardProject') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE AwardProject 
GO
CREATE 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())		--創建日期	
)
GO
IF 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=NULL
GO
--列描述
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'
GO
EXEC 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'
GO
EXEC 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'
GO
EXEC 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'
GO

SELECT * FROM dbo.AwardProject
GO


--抽獎員工表 Employee
IF EXISTS (select * from sysobjects where id = object_id(N'dbo.Employee') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE Employee 
GO
CREATE TABLE Employee
(
	EmployeeId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	EmployeeProjectId INT 
		FOREIGN KEY REFERENCES AwardProject(ProjectId),		--外鍵,項目ID
	EmployeeNO VARCHAR(30) NOT NULL,    --員工編號
	EmployeeName NVARCHAR(100) NOT NULL, --員工姓名
	EmployeeMobile VARCHAR(20) NOT NULL,	--手機號碼
											--skype
	EmployeeDate 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=NULL
GO
--列描述
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'
GO
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'EmployeeProjectId'
GO
EXEC 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'
GO
EXEC 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'
GO
EXEC 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'
GO
EXEC 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'
GO

SELECT * FROM dbo.Employee
GO



--奖项表 AwardType
IF EXISTS (select * from sysobjects where id = object_id(N'dbo.AwardType') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE AwardType 
GO
CREATE TABLE AwardType
(
	AwardTypeId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	AwardProjectId INT 
		FOREIGN KEY REFERENCES AwardProject(ProjectId),		--外鍵,項目ID
	AwardName 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=NULL
GO
--列描述
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'
GO
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'AwardProjectId'
GO
EXEC 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'
GO
EXEC 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'
GO
EXEC 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'
GO
EXEC 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'
GO





SELECT * FROM dbo.AwardType
GO


--奖品表 AwardProduct
IF EXISTS (select * from sysobjects where id = object_id(N'dbo.AwardProduct') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE AwardProduct 
GO
CREATE TABLE AwardProduct
(
	ProductId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	ProductAwardTypeId	INT
		FOREIGN KEY REFERENCES AwardType(AwardTypeId),			--外鍵,獎項類型ID
	ProductName 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=NULL
GO
--列描述
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'
GO
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'ProductAwardTypeId'
GO
EXEC 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'
GO
EXEC 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'
GO
EXEC 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'
GO

SELECT * FROM dbo.AwardProduct
GO



--獎品圖片表
IF EXISTS (select * from sysobjects where id = object_id(N'dbo.AwardProductImage') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE AwardProductImage 
GO
CREATE TABLE AwardProductImage
(
	ProductImageId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	ProductImageProductId	INT
		FOREIGN KEY REFERENCES AwardProduct(ProductId),			--外鍵,產品ID
	ProductImageByte 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=NULL
GO
--列描述
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'
GO
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'ProductImageProductId'
GO
EXEC 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'
GO

SELECT * FROM dbo.AwardProductImage
GO


--获奖者列表 LuckyWinner  ---獎品确認收簽
IF EXISTS (select * from sysobjects where id = object_id(N'dbo.LuckyWinner') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE LuckyWinner 
GO
CREATE TABLE LuckyWinner
(
	WinnerId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	WinnerEmployeeId INT 
		FOREIGN KEY REFERENCES Employee(EmployeeId),		--外鍵,員工ID	
	WinnerAwardTypeId	INT
		FOREIGN KEY REFERENCES AwardType(AwardTypeId),		--外鍵,獎項類型ID	
	WinnerIsOk 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=NULL
GO
--列描述
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'
GO
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'WinnerEmployeeId'
GO
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'WinnerAwardTypeId'
GO
EXEC 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'
GO
EXEC 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'
GO
EXEC 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'
GO




SELECT * FROM dbo.LuckyWinner
GO


--所穫獎者發短信,SKYPE信息等 WinnerSms
IF EXISTS (select * from sysobjects where id = object_id(N'dbo.WinnerSms') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE WinnerSms 
GO
CREATE TABLE WinnerSms
(
	SmsId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	SmsEmployeeId INT 
		FOREIGN KEY REFERENCES Employee(EmployeeId),		--外鍵,員工ID
	SmsContent 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=NULL
GO
--列描述
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'
GO
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'SmsEmployeeId'
GO
EXEC 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'
GO
EXEC 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'
GO

SELECT * FROM dbo.WinnerSms
GO

--窗口背景圖
IF EXISTS (select * from sysobjects where id = object_id(N'dbo.FormBackground') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE FormBackground 
GO
CREATE TABLE FormBackground
(
	BackgroundId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
	BackgroundImage IMAGE,
	BackgroundFmName NVARCHAR(200)
)
GO
IF 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=NULL
GO
--列描述
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'
GO
EXEC 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'
GO
EXEC 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'
GO


SELECT * FROM dbo.FormBackground