sqlserver更新表脚本

--增加项目字段,门店信息，所在省份，所在市，所在区县，提供服务
ALTER TABLE [dbo].[school_base_info]
ADD [store_information] varchar(32) NULL
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'school_base_info',
'COLUMN', N'store_information')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'门店信息'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'school_base_info'
, @level2type = 'COLUMN', @level2name = N'store_information'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'门店信息'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'school_base_info'
, @level2type = 'COLUMN', @level2name = N'store_information'
GO

ALTER TABLE [dbo].[school_base_info]
ADD [store_in_province] varchar(32) NULL
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'school_base_info',
'COLUMN', N'store_in_province')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'所在省份'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'school_base_info'
, @level2type = 'COLUMN', @level2name = N'store_in_province'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'所在省份'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'school_base_info'
, @level2type = 'COLUMN', @level2name = N'store_in_province'
GO

ALTER TABLE [dbo].[school_base_info]
ADD [store_in_city] varchar(32) NULL
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'school_base_info',
'COLUMN', N'store_in_city')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'所在市'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'school_base_info'
, @level2type = 'COLUMN', @level2name = N'store_in_city'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'所在市'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'school_base_info'
, @level2type = 'COLUMN', @level2name = N'store_in_city'
GO

ALTER TABLE [dbo].[school_base_info]
ADD [store_in_area] varchar(32) NULL
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'school_base_info',
'COLUMN', N'store_in_area')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'所在区县'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'school_base_info'
, @level2type = 'COLUMN', @level2name = N'store_in_area'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'所在区县'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'school_base_info'
, @level2type = 'COLUMN', @level2name = N'store_in_area'
GO

ALTER TABLE [dbo].[school_base_info]
ADD [store_support] varchar(512) NULL
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'school_base_info',
'COLUMN', N'store_support')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'提供服务'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'school_base_info'
, @level2type = 'COLUMN', @level2name = N'store_support'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'提供服务'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'school_base_info'
, @level2type = 'COLUMN', @level2name = N'store_support'
GO

--增加会员信息表字段，邀请人ID，是否已下单,会员类型
ALTER TABLE [dbo].[sys_common_user]
ADD [invitor_id] varchar(32) NULL
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'sys_common_user',
'COLUMN', N'invitor_id')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'邀请人ID'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'sys_common_user'
, @level2type = 'COLUMN', @level2name = N'invitor_id'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'邀请人ID'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'sys_common_user'
, @level2type = 'COLUMN', @level2name = N'invitor_id'
GO

ALTER TABLE [dbo].[sys_common_user]
ADD [is_order] varchar(2) NULL
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'sys_common_user',
'COLUMN', N'is_order')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'是否已下单'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'sys_common_user'
, @level2type = 'COLUMN', @level2name = N'is_order'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'是否已下单'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'sys_common_user'
, @level2type = 'COLUMN', @level2name = N'is_order'
GO

ALTER TABLE [dbo].[sys_common_user]
ADD [user_type] varchar(32) NULL
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'sys_common_user',
'COLUMN', N'user_type')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'会员类型'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'sys_common_user'
, @level2type = 'COLUMN', @level2name = N'user_type'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'会员类型'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'sys_common_user'
, @level2type = 'COLUMN', @level2name = N'user_type'
GO

//增加商品表字段，商品低价
ALTER TABLE [dbo].[food_base_info]
ADD [floor] decimal(8,2) NULL
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'food_base_info',
'COLUMN', N'floor')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'商品底价'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'food_base_info'
, @level2type = 'COLUMN', @level2name = N'floor'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'商品底价'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'food_base_info'
, @level2type = 'COLUMN', @level2name = N'floor'
GO

ALTER TABLE [dbo].[food_base_info]
ADD [food_kind] varchar(2) NULL
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'food_base_info',
'COLUMN', N'kind')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'商品种类'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'food_base_info'
, @level2type = 'COLUMN', @level2name = N'kind'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'商品种类'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'food_base_info'
, @level2type = 'COLUMN', @level2name = N'kind'
GO

 

//增加订单表字段，修改次数，订单完成时间
ALTER TABLE [dbo].[order_main_info]
ADD [mod_time] varchar(2) NULL
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'order_main_info',
'COLUMN', N'mod_time')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'修改次数'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'order_main_info'
, @level2type = 'COLUMN', @level2name = N'mod_time'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'修改次数'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'order_main_info'
, @level2type = 'COLUMN', @level2name = N'mod_time'
GO

 

ALTER TABLE [dbo].[order_main_info]
ADD [complete_time] datetime NULL
IF ((SELECT COUNT(*) from fn_listextendedproperty('MS_Description',
'SCHEMA', N'dbo',
'TABLE', N'order_main_info',
'COLUMN', N'complete_time')) > 0)
EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'订单完成时间'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'order_main_info'
, @level2type = 'COLUMN', @level2name = N'complete_time'
ELSE
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'订单完成时间'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'order_main_info'
, @level2type = 'COLUMN', @level2name = N'complete_time'
GO