SQL Server更新脚本模板，多谢CRM组

--取出某个字段的数据类型
select b.name from syscolumns a,systypes b
where a.id=object_id('tIsuProblemCardPay') and a.name='ZfNo'
      and a.xtype=b.xtype


--增加字段的模版
if exists(select * from sysobjects where name='tPlcProject' and xtype='U')
begin 
   if not exists(select a.* from sysobjects a,syscolumns b 
                where a.id=b.id and a.name='tPlcProject' 
                     and a.xtype='U' and b.name='JfMxSaveType')
    alter table tPlcProject add JfMxSaveType varchar(1)  not null default('0')  
end 


--更新字段的默认值
 declare @sss varchar(100);


 select @sss=name from sysobjects where xtype='D' and id=
     (select b.cdefault from sysobjects a,syscolumns b where a.id=b.id and a.name='tIsuCardSaveHead'  and b.name='IsInvoice')
 --print @sss
 if @sss<>''
 begin
   exec('alter table tIsuCardSaveHead drop constraint ' +@sss)   
 end


 if exists ( select b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='tIsuCardSaveHead'  and b.name='IsInvoice')
   alter table tIsuCardSaveHead add default ('0') for IsInvoice

--更换表的所有者
EXEC sp_changeobjectowner  '',''


--在历史月表上增加字段
declare @vs_AccYearAndMonth varchar(20)
declare chCurTmp Cursor for 
    select (AcctYear+AcctMonth) as AccYearAndMonth  from tsysacctmonth where BgnDate>'2005-08-01' and EndDate<=convert(char(10),getdate(),120)
open chCurTmp
fetch chCurTmp into @vs_AccYearAndMonth
while @@fetch_status>=0
begin
   exec('if exists(select * from sysobjects where name=''tIsuCardPay' + @vs_AccYearAndMonth + ''' and xtype=''U'')and
         not exists(select a.* from sysobjects a,syscolumns b   where a.id=b.id and a.name=''tIsuCardPay' + @vs_AccYearAndMonth +
                    ''' and a.xtype=''U'' and b.name=''CardNoType'')   
          alter table  tIsuCardPay'+@vs_AccYearAndMonth+' add CardNoType varchar(1) null ')


   exec('if exists(select * from sysobjects where name=''tIsuCardPay' + @vs_AccYearAndMonth + ''' and xtype=''U'')and
         not exists(select a.* from sysobjects a,syscolumns b   where a.id=b.id and a.name=''tIsuCardPay' + @vs_AccYearAndMonth +
                    ''' and a.xtype=''U'' and b.name=''CardNo'')   
          alter table  tIsuCardPay'+@vs_AccYearAndMonth+' add CardNo varchar(100) null ')


  fetch chCurTmp into @vs_AccYearAndMonth
end
close chCurTmp
deallocate chCurTmp


--流程控制case语句
--和Oracle脚本一致


    (case <条件选择运算式>
      when<运算式>then<运算式>
       …
      when<运算式>then<运算式>
      [else<运算式>]
  end) as <别名>


实例（条件选择符完全相同，并且条件表达式为相同条件选择）：
  Select PluCode,PluName,ClsCode,
  (case ClsCode when '0010101' then '好家伙'
               when '0010102' then '坏家伙'
               when '0010103' then '还不错'
               else '不咋地' end) as INFO 
From Goods 
go
（多个条件进行不同比较时，那么必须在when子句中指定比较条件）：
Select PluCode,PluName,ClsCode,
  (case when ClsCode='0010101' then '好家伙'
       when ClsCode='0010102' then '坏家伙'
       when ClsCode='0010103' then '还不错'
       else '不咋地' end) as INFO 
From Goods 
go

--将一个表的某个字段数据更新到另外一个表中
update t2
set t2.Field2=t1.Field2
from  table1 t1,table2 t2
where t1.Field1=t2.Field1