最近在数据迁移，需要考虑到增量迁移，所以需要在老系统所有表加一个迁移更新时间字段

直接贴脚本，其中TRANSFER_UPDATE_TIM是我要加的字段名，设置默认值为当前时间，创建更新触发器

declare @sql varchar(1000), @name varchar(200), @idColumn nvarchar(50)
declare my_cursor scroll cursor for
    select name from sysobjects where type = 'u'
open my_cursor
fetch next from my_cursor into @name
while(@@fetch_status=0)
begin
    print 'processing ' + @name
		SET @idColumn = (Select isnull(name, '')  from syscolumns Where ID=OBJECT_ID(''+ @name +'') AND name = 'id');
		IF(@idColumn = 'id' AND CHARINDEX('webstarter_', @name) = 0 )
		BEGIN
	  SET @sql =  N' if not exists (select * from syscolumns where id=object_id('''+ @name + N''') and name=''TRANSFER_UPDATE_TIME'') ALTER TABLE ['+ @name + N'] ADD [TRANSFER_UPDATE_TIME] datetime DEFAULT getdate() NULL;'
		print @sql
		exec(@sql)
		SET @sql =  N' USE [数据库名称] 
									if exists(select * from sys.triggers where name= ''tgr_' + @name + N'_updtime'')
									drop trigger tgr_' + @name + N'_updtime;'
		print @sql
		exec(@sql)
		SET @sql =  N' USE [数据库名称] SET ANSI_NULLS ON'
		print @sql
		exec(@sql)
		SET @sql =  N' USE [数据库名称] SET QUOTED_IDENTIFIER ON;'
		print @sql
		exec(@sql)
		
		SET @sql =  N' CREATE TRIGGER [tgr_' + @name + N'_updtime] ON [' + @name + N'] AFTER UPDATE AS BEGIN SET NOCOUNT ON; UPDATE '  + @name + N'  SET TRANSFER_UPDATE_TIME=SYSDATETIME() WHERE ID IN (SELECT DISTINCT ID FROM inserted) END'
		print @sql
		exec(@sql)
    print 'finished'
		END
    fetch next from my_cursor into @name
end
close my_cursor
deallocate my_cursor