MS SQL Server 建庫建表

標籤：

CREATE DATABASE Test

use Test

--建立使用者類型表
CREATE TABLE UserType (
ID INT NOT NULL identity(1,1) primary key,
TypeName VARCHAR(200) DEFAULT NULL ,

TypeDescription VARCHAR(200) DEFAULT NULL ,
)
exec sys.sp_addextendedproperty @name=‘MS_Description‘,@value= ‘自增長主鍵‘,@level0type=‘schema‘,@level0name=‘dbo‘,@level1type=‘table‘,@level1name=‘UserType‘,@LEVEL2TYPE=‘COLUMN‘,@level2name=‘ID‘
exec sys.sp_addextendedproperty @name=‘MS_DESCRIPTION‘,@value=‘使用者類型名稱‘,@level0type=‘schema‘,@level0name=‘dbo‘,@level1type=‘table‘,@level1name=‘UserType‘,@level2type=‘column‘,@level2name=‘TypeName‘
exec sys.sp_addextendedproperty @name=‘ms_description‘,@value=‘使用者類型描述‘,@level0type=‘schema‘,@level0name=‘dbo‘,@level1type=‘table‘,@level1name=‘UserType‘,@level2type=‘coloum‘,@level2name=‘TypeDescription‘

-- 建立一個使用者資訊資料表

CREATE TABLE UserInfo (
ID INT NOT NULL identity(1,1) primary key,
UserName VARCHAR(200) DEFAULT NULL ,
UserLogin VARCHAR(200) DEFAULT NULL ,
UserPassword VARCHAR(200) DEFAULT NULL ,
UserEmail VARCHAR(100) DEFAULT NULL ,
UserType int DEFAULT NULL ,
CONSTRAINT userinfo_ibfk_1 FOREIGN KEY (UserType) REFERENCES usertype (ID)
)
exec sys.sp_addextendedproperty @name=‘MS_Description‘,@value= ‘自增長主鍵‘,@level0type=‘schema‘,@level0name=‘dbo‘,@level1type=‘table‘,@level1name=‘UserInfo‘,@LEVEL2TYPE=‘COLUMN‘,@level2name=‘ID‘
exec sys.sp_addextendedproperty @name=‘MS_DESCRIPTION‘,@value=‘使用者姓名‘,@level0type=‘schema‘,@level0name=‘dbo‘,@level1type=‘table‘,@level1name=‘UserInfo‘,@level2type=‘column‘,@level2name=‘UserName‘
exec sys.sp_addextendedproperty @name=‘ms_description‘,@value=‘使用者登入名稱‘,@level0type=‘schema‘,@level0name=‘dbo‘,@level1type=‘table‘,@level1name=‘UserInfo‘,@level2type=‘coloum‘,@level2name=‘UserLogin‘
exec sys.sp_addextendedproperty @name=‘MS_Description‘,@value= ‘密碼‘,@level0type=‘schema‘,@level0name=‘dbo‘,@level1type=‘table‘,@level1name=‘UserInfo‘,@LEVEL2TYPE=‘COLUMN‘,@level2name=‘UserPassword‘
exec sys.sp_addextendedproperty @name=‘MS_DESCRIPTION‘,@value=‘電子郵箱‘,@level0type=‘schema‘,@level0name=‘dbo‘,@level1type=‘table‘,@level1name=‘UserInfo‘,@level2type=‘column‘,@level2name=‘UserEmail‘
exec sys.sp_addextendedproperty @name=‘ms_description‘,@value=‘使用者類型‘,@level0type=‘schema‘,@level0name=‘dbo‘,@level1type=‘table‘,@level1name=‘UserInfo‘,@level2type=‘coloum‘,@level2name=‘UserType‘

MS SQL Server 建庫建表