mirror of
https://github.com/dotnetcore/Util.git
synced 2026-10-20 22:26:14 +08:00
何镇汐
708 lines
21 KiB
Transact-SQL
708 lines
21 KiB
Transact-SQL
/*========================================================== 1. 创建数据库 ===========================================================*/
|
|
USE [master]
|
|
GO
|
|
|
|
--删除数据库
|
|
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'UtilTest'
|
|
GO
|
|
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'UtilTest')
|
|
Begin
|
|
DROP DATABASE [UtilTest]
|
|
End
|
|
GO
|
|
|
|
--创建数据库
|
|
CREATE DATABASE [UtilTest]
|
|
GO
|
|
|
|
/*========================================================== 2. 创建架构 ===========================================================*/
|
|
USE [UtilTest]
|
|
GO
|
|
|
|
/* 1. Sales */
|
|
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Sales')
|
|
DROP SCHEMA [Sales]
|
|
GO
|
|
CREATE SCHEMA [Sales] AUTHORIZATION [dbo]
|
|
GO
|
|
|
|
/* 2. Productions */
|
|
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Productions')
|
|
DROP SCHEMA [Productions]
|
|
GO
|
|
CREATE SCHEMA [Productions] AUTHORIZATION [dbo]
|
|
GO
|
|
|
|
/* 3. Customers */
|
|
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Customers')
|
|
DROP SCHEMA [Customers]
|
|
GO
|
|
CREATE SCHEMA [Customers] AUTHORIZATION [dbo]
|
|
GO
|
|
|
|
|
|
/*========================================================== 3. 创建表 ===========================================================*/
|
|
|
|
if exists (select 1
|
|
from sysobjects
|
|
where id = object_id('Customers.Customers')
|
|
and type = 'U')
|
|
drop table Customers.Customers
|
|
go
|
|
|
|
/*==============================================================*/
|
|
/* Table: Customers */
|
|
/*==============================================================*/
|
|
create table Customers.Customers (
|
|
CustomerId nvarchar(50) not null,
|
|
Name nvarchar(20) not null,
|
|
Nickname nvarchar(30) null,
|
|
Balance decimal(18,2) not null,
|
|
Gender int null,
|
|
Tel nvarchar(20) null,
|
|
Mobile nvarchar(20) null,
|
|
Email nvarchar(100) null,
|
|
CreationTime datetime null,
|
|
CreatorId uniqueidentifier null,
|
|
LastModificationTime datetime null,
|
|
LastModifierId uniqueidentifier null,
|
|
Version timestamp null,
|
|
constraint PK_CUSTOMERS primary key (CustomerId)
|
|
)
|
|
go
|
|
|
|
if exists (select 1 from sys.extended_properties
|
|
where major_id = object_id('Customers.Customers') and minor_id = 0)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Customers', 'table', 'Customers'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'客户',
|
|
'schema', 'Customers', 'table', 'Customers'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Customers.Customers')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'CustomerId')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'CustomerId'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'客户编号',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'CustomerId'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Customers.Customers')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'Name')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'Name'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'客户名称',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'Name'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Customers.Customers')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'Nickname')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'Nickname'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'昵称',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'Nickname'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Customers.Customers')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'Balance')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'Balance'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'余额',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'Balance'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Customers.Customers')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'Gender')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'Gender'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'性别',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'Gender'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Customers.Customers')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'Tel')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'Tel'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'联系电话',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'Tel'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Customers.Customers')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'Mobile')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'Mobile'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'手机号',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'Mobile'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Customers.Customers')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'Email')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'Email'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'电子邮件',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'Email'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Customers.Customers')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'CreationTime')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'CreationTime'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'创建时间',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'CreationTime'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Customers.Customers')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'CreatorId')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'CreatorId'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'创建人',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'CreatorId'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Customers.Customers')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'LastModificationTime')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'LastModificationTime'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'最后修改时间',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'LastModificationTime'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Customers.Customers')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'LastModifierId')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'LastModifierId'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'最后修改人',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'LastModifierId'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Customers.Customers')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'Version')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'Version'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'版本号',
|
|
'schema', 'Customers', 'table', 'Customers', 'column', 'Version'
|
|
go
|
|
|
|
|
|
|
|
|
|
|
|
|
|
if exists (select 1
|
|
from sysobjects
|
|
where id = object_id('Productions.Products')
|
|
and type = 'U')
|
|
drop table Productions.Products
|
|
go
|
|
|
|
/*==============================================================*/
|
|
/* Table: Products */
|
|
/*==============================================================*/
|
|
create table Productions.Products (
|
|
ProductId int not null,
|
|
Code nvarchar(30) not null,
|
|
Name nvarchar(200) not null,
|
|
Extends nvarchar(max) null,
|
|
Price decimal(10,2) null,
|
|
IsDeleted bit not null,
|
|
Version timestamp null,
|
|
constraint PK_PRODUCTS primary key (ProductId)
|
|
)
|
|
go
|
|
|
|
if exists (select 1 from sys.extended_properties
|
|
where major_id = object_id('Productions.Products') and minor_id = 0)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Productions', 'table', 'Products'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'商品',
|
|
'schema', 'Productions', 'table', 'Products'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Productions.Products')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'ProductId')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Productions', 'table', 'Products', 'column', 'ProductId'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'商品编号',
|
|
'schema', 'Productions', 'table', 'Products', 'column', 'ProductId'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Productions.Products')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'Code')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Productions', 'table', 'Products', 'column', 'Code'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'商品编码',
|
|
'schema', 'Productions', 'table', 'Products', 'column', 'Code'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Productions.Products')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'Name')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Productions', 'table', 'Products', 'column', 'Name'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'商品名称',
|
|
'schema', 'Productions', 'table', 'Products', 'column', 'Name'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Productions.Products')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'Extends')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Productions', 'table', 'Products', 'column', 'Extends'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'扩展属性',
|
|
'schema', 'Productions', 'table', 'Products', 'column', 'Extends'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Productions.Products')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'Price')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Productions', 'table', 'Products', 'column', 'Price'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'价格',
|
|
'schema', 'Productions', 'table', 'Products', 'column', 'Price'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Productions.Products')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'IsDeleted')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Productions', 'table', 'Products', 'column', 'IsDeleted'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'是否删除',
|
|
'schema', 'Productions', 'table', 'Products', 'column', 'IsDeleted'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Productions.Products')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'Version')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Productions', 'table', 'Products', 'column', 'Version'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'版本号',
|
|
'schema', 'Productions', 'table', 'Products', 'column', 'Version'
|
|
go
|
|
|
|
|
|
|
|
|
|
|
|
|
|
if exists (select 1
|
|
from sysobjects
|
|
where id = object_id('Sales.Orders')
|
|
and type = 'U')
|
|
drop table Sales.Orders
|
|
go
|
|
|
|
/*==============================================================*/
|
|
/* Table: Orders */
|
|
/*==============================================================*/
|
|
create table Sales.Orders (
|
|
OrderId uniqueidentifier not null,
|
|
Code nvarchar(30) not null,
|
|
Name nvarchar(200) not null,
|
|
Version timestamp null,
|
|
constraint PK_ORDERS primary key (OrderId)
|
|
)
|
|
go
|
|
|
|
if exists (select 1 from sys.extended_properties
|
|
where major_id = object_id('Sales.Orders') and minor_id = 0)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Sales', 'table', 'Orders'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'订单',
|
|
'schema', 'Sales', 'table', 'Orders'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Sales.Orders')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'OrderId')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Sales', 'table', 'Orders', 'column', 'OrderId'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'订单编号',
|
|
'schema', 'Sales', 'table', 'Orders', 'column', 'OrderId'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Sales.Orders')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'Code')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Sales', 'table', 'Orders', 'column', 'Code'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'订单编码',
|
|
'schema', 'Sales', 'table', 'Orders', 'column', 'Code'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Sales.Orders')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'Name')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Sales', 'table', 'Orders', 'column', 'Name'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'订单名称',
|
|
'schema', 'Sales', 'table', 'Orders', 'column', 'Name'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Sales.Orders')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'Version')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Sales', 'table', 'Orders', 'column', 'Version'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'版本号',
|
|
'schema', 'Sales', 'table', 'Orders', 'column', 'Version'
|
|
go
|
|
|
|
|
|
|
|
|
|
if exists (select 1
|
|
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
|
|
where r.fkeyid = object_id('Sales.OrderItems') and o.name = 'FK_ORDERITE_REFERENCE_PRODUCTS')
|
|
alter table Sales.OrderItems
|
|
drop constraint FK_ORDERITE_REFERENCE_PRODUCTS
|
|
go
|
|
|
|
if exists (select 1
|
|
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
|
|
where r.fkeyid = object_id('Sales.OrderItems') and o.name = 'FK_ORDERITE_REFERENCE_ORDERS')
|
|
alter table Sales.OrderItems
|
|
drop constraint FK_ORDERITE_REFERENCE_ORDERS
|
|
go
|
|
|
|
if exists (select 1
|
|
from sysobjects
|
|
where id = object_id('Sales.OrderItems')
|
|
and type = 'U')
|
|
drop table Sales.OrderItems
|
|
go
|
|
|
|
/*==============================================================*/
|
|
/* Table: OrderItems */
|
|
/*==============================================================*/
|
|
create table Sales.OrderItems (
|
|
OrderItemId uniqueidentifier not null,
|
|
OrderId uniqueidentifier not null,
|
|
ProductId int not null,
|
|
ProductName nvarchar(200) not null,
|
|
Quantity int not null,
|
|
Price decimal(10,2) not null,
|
|
constraint PK_ORDERITEMS primary key (OrderItemId)
|
|
)
|
|
go
|
|
|
|
if exists (select 1 from sys.extended_properties
|
|
where major_id = object_id('Sales.OrderItems') and minor_id = 0)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Sales', 'table', 'OrderItems'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'订单明细',
|
|
'schema', 'Sales', 'table', 'OrderItems'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Sales.OrderItems')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'OrderItemId')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Sales', 'table', 'OrderItems', 'column', 'OrderItemId'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'订单明细编号',
|
|
'schema', 'Sales', 'table', 'OrderItems', 'column', 'OrderItemId'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Sales.OrderItems')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'OrderId')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Sales', 'table', 'OrderItems', 'column', 'OrderId'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'订单编号',
|
|
'schema', 'Sales', 'table', 'OrderItems', 'column', 'OrderId'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Sales.OrderItems')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'ProductId')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Sales', 'table', 'OrderItems', 'column', 'ProductId'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'商品编号',
|
|
'schema', 'Sales', 'table', 'OrderItems', 'column', 'ProductId'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Sales.OrderItems')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'ProductName')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Sales', 'table', 'OrderItems', 'column', 'ProductName'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'商品名称',
|
|
'schema', 'Sales', 'table', 'OrderItems', 'column', 'ProductName'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Sales.OrderItems')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'Quantity')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Sales', 'table', 'OrderItems', 'column', 'Quantity'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'数量',
|
|
'schema', 'Sales', 'table', 'OrderItems', 'column', 'Quantity'
|
|
go
|
|
|
|
if exists(select 1 from sys.extended_properties p where
|
|
p.major_id = object_id('Sales.OrderItems')
|
|
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'Price')
|
|
)
|
|
begin
|
|
execute sp_dropextendedproperty 'MS_Description',
|
|
'schema', 'Sales', 'table', 'OrderItems', 'column', 'Price'
|
|
|
|
end
|
|
|
|
|
|
execute sp_addextendedproperty 'MS_Description',
|
|
'单价',
|
|
'schema', 'Sales', 'table', 'OrderItems', 'column', 'Price'
|
|
go
|
|
|
|
alter table Sales.OrderItems
|
|
add constraint FK_ORDERITE_REFERENCE_PRODUCTS foreign key (ProductId)
|
|
references Productions.Products (ProductId)
|
|
go
|
|
|
|
alter table Sales.OrderItems
|
|
add constraint FK_ORDERITE_REFERENCE_ORDERS foreign key (OrderId)
|
|
references Sales.Orders (OrderId)
|
|
go
|