判断多个表是否加了默认值脚本

set nocount on
create table #tb(id int identity(1,1), tbname varchar(100))
insert into #tb(tbname)
select 'tbCustUse_gold'
union all select 'tbCustUse_PJ'
union all select 'tbCustUse_PJ'
union all select 'tbOrderBalance_report_dtl'
union all select 'tbOrderBalance_report_gold'
union all select 'tbOrderBalance_report_pj'
union all select 'tbOrderBalance_report_stone'
union all select 'tbOrderBalance_report_sum'
union all select 'tbOrderBalance_StonePrint'
union all select 'tbOrderBalance_StonePrint_cust'

declare @maxid int,@i int
select @maxid=max(id) from #tb
set @i=1

while @i<=@maxid
begin

 declare @cur cursor,@name varchar(100),@type varchar(50)
 declare @table varchar(100),@s varchar(2000)
 select @table=tbname from #tb where id=@i
 set @cur=cursor for select a.name,b.name from syscolumns a inner join systypes b on a.xtype=b.xtype where a.id=object_id(@table)
 open @cur
 fetch next from @cur into @name,@type
 while @@fetch_status=0
 begin
  set @s=''
  set @s='if not exists(select d.name from syscolumns a join sysobjects b on a.id=b.id
 join syscomments c on a.cdefault=c.id join sysobjects d on c.id=d.id
 where b.name= '''+@table+''' and a.name='''+@name+''')
alter table '+@table+' add constraint DF_'+@table+'_'+@name+' default ('+
  case when charindex('int',@type)>0 or @type='numeric' then '0'
   when @type='datetime' then 'getdate()' else '''''' end +') for '+ @name+ char(10)+
  'GO'
  print @s
  fetch next from @cur into @name,@type
 end
 close @cur
 deallocate @cur
 set @i=@i+1
end

drop table #tb