sqlserver 修改表结构

因为我们公司所有对数据库的修改都要保留脚本，所以就不能直接手动改了

脚本

1.字段

1.1新增

单个新增

alter table productInfo add productStatus VARCHAR(2) NULL 

批量新增

ALTER TABLE prodSubsysInfo ADD collocation VARCHAR ( 4 ) NULL,
releaseType VARCHAR ( 4 ) NULL,
offerType VARCHAR ( 4 ) NULL,
publicAttribute VARCHAR ( 4 ) NULL,
releaseVersion VARCHAR ( 4 ) NULL,
prodSubsysManager VARCHAR ( 4 ) NULL,
prodSubsysStatus VARCHAR ( 4 ) NULL

1.2 修改

exec sp_rename '[prodSubsysInfo].[ModuleIdParent]','subSysIdParent'

2.表

IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[AppSystemInfo]') AND type IN ('U'))
	DROP TABLE [dbo].[AppSystemInfo]
GO

CREATE TABLE [dbo].[AppSystemInfo] (
  [systemId] varchar(20) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [systemName] varchar(20) COLLATE Chinese_PRC_CI_AS  NULL,
  [systemManager] varchar(20) COLLATE Chinese_PRC_CI_AS  NULL,
  [systemStatus] varchar(2) COLLATE Chinese_PRC_CI_AS  NULL,
  [directions] varchar(500) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[AppSystemInfo] SET (LOCK_ESCALATION = TABLE)
GO