-----------------------20151118诊断--------------------------------------------------
if exists(select * from syscolumns where object_id('LAB1')=id and name='LAB18' and length= 128)
alter table LAB1 alter column LAB18 varchar(256)
go
if exists(select * from syscolumns where object_id('LAB2')=id and name='LAB18' and length= 128)
alter table LAB2 alter column LAB18 varchar(256)
go
if exists(select * from syscolumns where object_id('VBL1')=id and name='VBL02' and length= 20)
alter table VBL1 alter column VBL02 varchar(30)
go
if exists(select * from syscolumns where object_id('VBL2')=id and name='VBL02' and length= 20)
alter table VBL2 alter column VBL02 varchar(30)
go
-----------------------20171208--------------------------------------------------
if not exists(select * from syscolumns where id=object_id('LAA1_2') and name='BCE01A')
alter table LAA1_2 add BCE01A int
go
if not exists(select * from syscolumns where id=OBJECT_ID('LAA1_2') and name='BCE01D')
alter table LAA1_2 add BCE01D int
go
if not exists(select * from syscolumns where id=OBJECT_ID('LAA1_2') and name='BCE03D')
alter table LAA1_2 add BCE03D varchar(20)
go
if not exists(select * from syscolumns where id=OBJECT_ID('LAA1_2') and name='LAA48')
alter table LAA1_2 add LAA48 datetime
go
if not exists(select * from syscolumns where id=OBJECT_ID('LAA1_2') and name='BCE01E')
alter table LAA1_2 add BCE01E int
go
if not exists(select * from syscolumns where id=OBJECT_ID('LAA1_2') and name='BCE03E')
alter table LAA1_2 add BCE03E varchar(20)
go
if not exists(select * from syscolumns where id=OBJECT_ID('LAA1_2') and name='LAA51')
alter table LAA1_2 add LAA51 datetime
go

--dev
if exists(select * from syscolumns where id = object_id('VBI2_dev') and name = 'VBI35' and xtype = 56)
begin
  declare @DefName varchar(255)
  set @DefName = (select object_name(cdefault) from syscolumns where id = object_id('VBI2_dev') and name = 'VBI35')
  if @DefName is not null
    exec ('ALTER TABLE VBI2_dev drop CONSTRAINT ' + @DefName)

  alter table VBI2_dev alter column VBI35 numeric(18,4)

  if @DefName is not null
    exec ('ALTER TABLE VBI2_dev add CONSTRAINT ' + @DefName + ' DEFAULT (0) FOR VBI35')
end
go
if exists(select * from syscolumns where object_id('FAF2')=id and name='FAF18' and length= 32)
alter table FAF2 alter column FAF18 varchar(64)
go

if not exists(select * from syscolumns where name = 'FAF41' and object_Name(id)='FAF2')
begin
Alter table FAF2
Add FAF41 tinyint default(0) with values
end
go

if not exists(select * from syscolumns where name = 'CBM28' and object_Name(id)='CBM2_dev')
begin
  alter table CBM2_dev add CBM28 varchar(64) --代办人姓名
  alter table CBM2_dev add CBM29 varchar(20) --代办人身份证号
end
go
if not exists(select * from syscolumns where name = 'CBM30' and object_Name(id)='CBM2_dev')
alter table CBM2_dev add CBM30 datetime
go
if not exists(select * from syscolumns where name = 'Crypt' and object_Name(id)='VAF2_dev')
  alter table VAF2_dev add Crypt tinyint default(0) with values
go
if not exists(select * from syscolumns where name = 'VAJ72' and object_Name(id)='VAJ2_dev')  
  alter table VAJ2_dev add VAJ72 tinyint default(0)
go
if not exists(select * from syscolumns where name = 'VBQ36' and object_Name(id)='VBQ2_dev')
 alter table VBQ2_dev add VBQ36 datetime
if not exists(select * from syscolumns where name = 'VBQ37' and object_Name(id)='VBQ2_dev') 
 alter table VBQ2_dev add VBQ37 datetime  
go
alter table VBU3 alter column VCB04 varchar(64)
alter table VCA1 alter column VCB04 varchar(64)
alter table VCA3 alter column VCB04 varchar(64)
GO
if not exists(select * from syscolumns where name = 'VCJ41' and object_Name(id)='VCJ1')
  alter table VCJ1 add VCJ41 varchar(64) --平台预约挂号编号
go  
if not exists(select * from syscolumns where name = 'VCJ42' and object_Name(id)='VCJ1')
  alter table VCJ1 add VCJ42 varchar(20) --平台预约取号密码
go  

if not exists(select * from syscolumns where name = 'BCE01A' and object_Name(id)='CBM7')
ALTER TABLE  CBM7 ADD BCE01A int default(0)
GO
if not exists(select * from syscolumns where name = 'CBM27' and object_Name(id)='CBM7')
ALTER TABLE  CBM7 ADD CBM27 int default(0)
GO
if not exists(select * from syscolumns where name = 'CBM30' and object_Name(id)='CBM7')
  alter table CBM7 add CBM30 datetime
go
if not exists(select * from syscolumns where name = 'CBM31' and object_Name(id)='CBM7')
  alter table CBM7 add CBM31 bigint --第三方序列号(唯一)，用于上传给第三方
go
if not exists(select * from syscolumns where name = 'CBM32' and object_Name(id)='CBM7')
  alter table CBM7 add CBM32 tinyint default(0) --慢病续方标示 0=否 1=慢病续方
go
if not exists(select * from syscolumns where name = 'CBM33' and object_Name(id)='CBM7')
  alter table CBM7 add CBM33 tinyint default(0) --处方审核标示
go
if not exists(select * from syscolumns where name = 'CBM34' and object_Name(id)='CBM7')
  alter table CBM7 add CBM34 varchar(10) --代办人年龄
go
if not exists(select * from syscolumns where name = 'CBM35' and object_Name(id)='CBM7')
  alter table CBM7 add CBM35 varchar(4) --代办人性别
go
if not exists(select * from syscolumns where name = 'CBM36' and object_Name(id)='CBM7')
  alter table CBM7 add CBM36 varchar(20) --代办人联系电话
go
if not exists(select * from syscolumns where name = 'CBM37' and object_Name(id)='CBM7')
  alter table CBM7 add CBM37 datetime --慢病续方有效期
go
if not exists(select * from syscolumns where name = 'BCE01B' and object_Name(id)='CBM7')
  alter table CBM7 add BCE01B int --介绍人id
go
if not exists(select * from syscolumns where name = 'BCE03B' and object_Name(id)='CBM7')
  alter table CBM7 add BCE03B varchar(64) --介绍人名称
go
if exists(select * from syscolumns where object_id('VAF7')=id and name='VAF23' and length< 512)
begin
  Alter table VAF7 Alter column VAF23 varchar(512)
end
go
if not exists(select * from syscolumns where name = 'SCF01' and object_Name(id)='VAI7')
  alter table VAI7 add SCF01 int
go
if not exists(select * from syscolumns where name = 'VAI37' and object_Name(id)='VAI7')
begin
  Alter table VAI7
  Add VAI37 Datetime default('1900-01-01') 
end
go

if  exists(select * from syscolumns where name = 'BCE02A' and object_Name(id)='VAI1' and length<20)
begin
  Alter table VAI1 
  Alter column BCE02A varchar(20)
end
Go

if  exists(select * from syscolumns where name = 'BCE02B' and object_Name(id)='VAI1'  and length<20)
begin
  Alter table VAI1 
  Alter column BCE02B varchar(20)
end
Go

if  exists(select * from syscolumns where name = 'BCE02A' and object_Name(id)='VAI2' and length<20)
begin
  Alter table VAI2 
  Alter column BCE02A varchar(20)
end
Go

if  exists(select * from syscolumns where name = 'BCE02B' and object_Name(id)='VAI2' and length<20)
begin
  Alter table VAI2 
  Alter column BCE02B varchar(20)
end
Go

if  exists(select * from syscolumns where name = 'BCE02A' and object_Name(id)='VAI1_2' and length<20)
begin
  Alter table VAI1_2 
  Alter column BCE02A varchar(20)
end
Go

if  exists(select * from syscolumns where name = 'BCE02B' and object_Name(id)='VAI1_2'  and length<20)
begin
  Alter table VAI1_2 
  Alter column BCE02B varchar(20)
end
Go

if  exists(select * from syscolumns where name = 'BCE02A' and object_Name(id)='VAI2_2' and length<20)
begin
  Alter table VAI2_2 
  Alter column BCE02A varchar(20)
end
Go

if  exists(select * from syscolumns where name = 'BCE02B' and object_Name(id)='VAI2_2' and length<20)
begin
  Alter table VAI2_2 
  Alter column BCE02B varchar(20)
end
Go


if  exists(select * from syscolumns where name = 'BCE02A' and object_Name(id)='VAI7' and length<20)
begin
  Alter table VAI7
  Alter column BCE02A varchar(20)
end
Go

if  exists(select * from syscolumns where name = 'BCE02B' and object_Name(id)='VAI7' and length<20)
begin
  Alter table VAI7 
  Alter column BCE02B varchar(20)
end
Go
if not exists(select * from syscolumns where name = 'BBY01A' and object_Name(id)='VBD7')
  alter table VBD7 add BBY01A int
go
if not exists(select * from syscolumns where name = 'VBD14' and object_Name(id)='VBD7')
  alter table VBD7 add VBD14 datetime
go
if not exists(select * from syscolumns where name = 'BCE01' and object_Name(id)='VBD7')
  alter table VBD7 add BCE01 int
go
if not exists(select * from syscolumns where name = 'BCE03' and object_Name(id)='VBD7')
  alter table VBD7 add BCE03 varchar(64)
go
if  exists(select * from syscolumns where name = 'VAJ23' and object_Name(id)='VAJ1' and xtype=48)
begin
  Alter table VAJ1  Alter column VAJ23 int
end
Go
if  exists(select * from syscolumns where name = 'VAJ23' and object_Name(id)='VAJ1_2' and xtype=48)
begin
  Alter table VAJ1_2  Alter column VAJ23 int
end
Go
if  exists(select * from syscolumns where name = 'VAJ23' and object_Name(id)='VAJ2' and xtype=48)
begin
  Alter table VAJ2  Alter column VAJ23 int
end
Go
if  exists(select * from syscolumns where name = 'VAJ23' and object_Name(id)='VAJ2_2' and xtype=48)
begin
  Alter table VAJ2_2  Alter column VAJ23 int
end
Go
if  exists(select * from syscolumns where name = 'VAJ23' and object_Name(id)='VAJ7' and xtype=48)
begin
  Alter table VAJ7  Alter column VAJ23 int
end
Go
if  exists(select * from syscolumns where name = 'VAJ32' and object_Name(id)='VAJ7' and xscale=4)
begin
  Alter table VAJ7  Alter column VAJ32 numeric(18,6)
end
Go
if not exists(select * from syscolumns where name = 'VAI35' and object_Name(id)='VAI7')
  alter table VAI7 add VAI35 bigint --第三方序列号(唯一)，用于上传给第三方
go

if not exists(select * from syscolumns where name = 'SCF01' and object_Name(id)='VAK7')
begin
  Alter table VAK7  Add SCF01 int default(0) with values
end
go

if not exists(select * from syscolumns where name = 'BCE01E' and object_Name(id)='VAJ7')
begin
  Alter table VAJ7  Add BCE01E int 
end
go
if not exists(select * from syscolumns where name = 'BCE01F' and object_Name(id)='VAJ7')
begin
  Alter table VAJ7  Add BCE01F int
end
go
if not exists(select * from syscolumns where name = 'BCE03E' and object_Name(id)='VAJ7')
begin
  Alter table VAJ7  Add BCE03E varchar(64)
end
go
if not exists(select * from syscolumns where name = 'BCQ04' and object_Name(id)='VAJ7')
begin
  Alter table VAJ7  Add BCQ04 varchar(20)
end
go

if Exists(select * from SysColumns where Object_id('VAF1') = id and name = 'VAF14' and length < 128)
begin
  alter table VAF1 alter column VAF14 varchar(128)
end
go
if Exists(select * from SysColumns where Object_id('VAF1') = id and name = 'VAF15' and length < 256)
begin
  alter table VAF1 alter column VAF15 varchar(256)
end
go
if Exists(select * from SysColumns where Object_id('VAF2') = id and name = 'VAF14' and length < 128)
begin
  alter table VAF2 alter column VAF14 varchar(128)
end
go
if Exists(select * from SysColumns where Object_id('VAF2') = id and name = 'VAF15' and length < 256)
begin
  alter table VAF2 alter column VAF15 varchar(256)
end
go
if Exists(select * from SysColumns where Object_id('VAF1_2') = id and name = 'VAF14' and length < 128)
begin
  alter table VAF1_2 alter column VAF14 varchar(128)
end
go
if Exists(select * from SysColumns where Object_id('VAF1_2') = id and name = 'VAF15' and length < 256)
begin
  alter table VAF1_2 alter column VAF15 varchar(256)
end
go
if Exists(select * from SysColumns where Object_id('VAF2_2') = id and name = 'VAF14' and length < 128)
begin
  alter table VAF2_2 alter column VAF14 varchar(128)
end
go
if Exists(select * from SysColumns where Object_id('VAF2_2') = id and name = 'VAF15' and length < 256)
begin
  alter table VAF2_2 alter column VAF15 varchar(256)
end
go
if Exists(select * from SysColumns where Object_id('VAF7') = id and name = 'VAF14' and length < 128)
begin
  alter table VAF7 alter column VAF14 varchar(128)
end
go
if Exists(select * from SysColumns where Object_id('VAF7') = id and name = 'VAF15' and length < 256)
begin
  alter table VAF7 alter column VAF15 varchar(256)
end
go
if Exists(select * from SysColumns where Object_id('VAFT') = id and name = 'VAF14' and length < 128)
begin
  alter table VAFT alter column VAF14 varchar(128)
end
go
if Exists(select * from SysColumns where Object_id('VAFT') = id and name = 'VAF15' and length < 256)
begin
  alter table VAFT alter column VAF15 varchar(256)
end
go
if Exists(select * from SysColumns where Object_id('LAB2') = id and name = 'VAA05' and length < 256)
begin
  DROP INDEX [IX_LAB2_VAA05] ON [dbo].[LAB2]
  alter table LAB2 alter column VAA05 nvarchar(128)
end
go
if Exists(select * from SysColumns where Object_id('VBD2') = id and name = 'DFlag' and xtype <> 48)
ALTER TABLE VBD2 alter column DFlag tinyint