产品手册 实施方案 用户案例 问题中心

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

分类:产品中心 产品手册 实施方案 128
— 检测最近两个月消息表,如果有,则检测是否 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
标签:脚本 上一篇: 下一篇:
展开更多
预约软件体验

loading...