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