合同消息提醒數(shù)據(jù)庫(kù)表變更

— 檢測(cè)最近兩個(gè)月消息表，如果有，則檢測(cè)是否 C_SYNC 字段，如果沒有，則自動(dòng)增加此字段

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 )

? ? — 設(shè)置 同步標(biāo)記 為 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 )

? ? — 設(shè)置 同步標(biāo)記 為 Y

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

? ? EXEC( @strSQL )

? END

?END

?— Mobile 上個(gè)月消息表

?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 )

? ? — 設(shè)置 同步標(biāo)記 為 Y

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

? ? EXEC( @strSQL )

? END

?END

?— STK 上個(gè)月消息表

?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 )

? ? — 設(shè)置 同步標(biāo)記 為 Y

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

? ? EXEC( @strSQL )

? END

?END

END

GO