floor mod sqlserver_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