合同消息提醒数据库表变更

— 检测最近两个月消息表，如果有，则检测是否 C_SYNC 字段，如果没有，则自动增加此字段

BEGIN

 declare @strDate as char(6)

 declare @strDBTable as varchar(24)

 declare @strSQL as varchar(1024)

 — Mobile 本月消息表

 Set @strDate = left( Convert( char(8), GetDate(), 112 ), 6 )

 Set @strDBTable = ‘OI_STK_MOBILE_MSG_’ + @strDate

 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@strDBTable) AND OBJECTPROPERTY(id, N’IsUserTable’) = 1)

 BEGIN

  IF NOT EXISTS (select * from syscolumns where id = object_id(@strDBTable) and name= ‘C_SYNC’)

  BEGIN

    Set @strSQL = ‘ALTER TABLE ‘ + @strDBTable + ‘ ADD [C_SYNC] [char](1) NOT NULL CONSTRAINT DF_STK_MBMSG_’ + @strDate + ‘_C_SYNC DEFAULT ”0”’

    EXEC( @strSQL )

    — 设置 同步标记 为 Y

    Set @strSQL = ‘UPDATE ‘ + @strDBTable + ‘ SET C_SYNC = ”1”’

    EXEC( @strSQL )

  END

 END

 — STK 本月消息表

 Set @strDBTable = ‘OI_STK_MSG_’ + @strDate

 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@strDBTable) AND OBJECTPROPERTY(id, N’IsUserTable’) = 1)

 BEGIN

  IF NOT EXISTS (select * from syscolumns where id = object_id(@strDBTable) and name= ‘C_SYNC’)

  BEGIN

    Set @strSQL = ‘ALTER TABLE ‘ + @strDBTable + ‘ ADD [C_SYNC] [char](1) NOT NULL CONSTRAINT DF_STK_MSG_’ + @strDate + ‘_C_SYNC DEFAULT ”0”’

    EXEC( @strSQL )

    — 设置 同步标记 为 Y

    Set @strSQL = ‘UPDATE ‘ + @strDBTable + ‘ SET C_SYNC = ”1”’

    EXEC( @strSQL )

  END

 END

 — Mobile 上个月消息表

 Set @strDate = left( Convert( char(8), DATEADD( MONTH, -1, GetDate() ), 112 ), 6 )

 Set @strDBTable = ‘OI_STK_MOBILE_MSG_’ + @strDate

 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@strDBTable) AND OBJECTPROPERTY(id, N’IsUserTable’) = 1)

 BEGIN

  IF NOT EXISTS (select * from syscolumns where id = object_id(@strDBTable) and name= ‘C_SYNC’)

  BEGIN

    Set @strSQL = ‘ALTER TABLE ‘ + @strDBTable + ‘ ADD [C_SYNC] [char](1) NOT NULL CONSTRAINT DF_STK_MBMSG_’ + @strDate + ‘_C_SYNC DEFAULT ”0”’

    EXEC( @strSQL )

    — 设置 同步标记 为 Y

    Set @strSQL = ‘UPDATE ‘ + @strDBTable + ‘ SET C_SYNC = ”1”’

    EXEC( @strSQL )

  END

 END

 — STK 上个月消息表

 Set @strDBTable = ‘OI_STK_MSG_’ + @strDate

 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@strDBTable) AND OBJECTPROPERTY(id, N’IsUserTable’) = 1)

 BEGIN

  IF NOT EXISTS (select * from syscolumns where id = object_id(@strDBTable) and name= ‘C_SYNC’)

  BEGIN

    Set @strSQL = ‘ALTER TABLE ‘ + @strDBTable + ‘ ADD [C_SYNC] [char](1) NOT NULL CONSTRAINT DF_STK_MSG_’ + @strDate + ‘_C_SYNC DEFAULT ”0”’

    EXEC( @strSQL )

    — 设置 同步标记 为 Y

    Set @strSQL = ‘UPDATE ‘ + @strDBTable + ‘ SET C_SYNC = ”1”’

    EXEC( @strSQL )

  END

 END

END

GO