在SqlServer中将所有表的smalldatetime类型的字段改成datetime类型

 应用场景：将Access数据库导入到Sql Server中后日期类型的字段会变成SmallDateTime类型

这时如果要把SmallDateTime类型的字段统一改为DateTime类型

declare @tableName varchar(50)
declare @tableID int
declare @columnName varchar(50)
declare @sql varchar(8000)

set @tableName=''
set @columnName=''

declare cur cursor for
select name,id from syscolumns
where xtype=58                                --58对应的类型是smalldatetime (参考systypes表)

open cur

fetch next from cur into @columnName,@tableID

while @@fetch_status=0
begin
    select @tableName=name from sysobjects where id=@tableID
    set @sql =' alter table '+@tableName+' alter column '+@columnName+' datetime'

print @sql

    exec(@sql)
    fetch next from cur into @columnName,@tableID
end

close cur
deallocate cur