批量给 VCRTTIME 字段 创建Default 约束

/*
CRT User: Bean
CRT Date: 2011-10-28
Desc:
the script main job is  Create Batch Default(Getdate()) Constraint for table according the condition
if the table do not have the VCRTTIME column,system will not create .
*/
begin tran
Begin Try
declare @TableName varchar(50),
@SQL varchar(max)
declare mycursor cursor for 
select A.Name from sysobjects A 
where A.xtype='u' and 
convert(varchar(100),A.CRDATE,23)='2011-10-26'and 
Exists (select 1 from syscolumns where id=object_id(A.name) and name='VCRTTIME')
open mycursor 
fetch next from mycursor into @TableName
while(@@fetch_status=0)
begin
set @SQL='alter table '+@TableName+ ' add constraint '+@TableName+'_VCRTTIME '+'default(getdate()) for VCRTTIME'
exec(@SQL)
fetch next from mycursor into @TableName
End
close mycursor 
deallocate mycursor 
commit tran
End Try
Begin Catch
rollback tran
Declare @ErrorMessage varchar(max)
set @ErrorMessage= 'Error_Message='+convert(varchar(50),Error_message())+char(10)+
'Error_Line='+convert(varchar(50),Error_Line())+char(10)+
'ERROR_NUMBER='+convert(varchar(50),ERROR_NUMBER())
RaisError(@ErrorMessage,16,1)
End Catch