将当前数据库中所有表的smalldatetime 列改为nvarchar(20)

-- 将当前数据库中, 所有表的smalldatetime 列改为nvarchar(20)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

-- 如果列上有索引/默认值之类的依赖项, 则无法修改

EXEC sp_msforeachtable

@command1 = N'

DECLARE CUR CURSOR LOCAL

FOR

SELECT

N''ALTER TABLE ? ALTER COLUMN ''

+ QUOTENAME(C.name)

+ N''nvarchar(20)''

FROM syscolumns C, systypes T

WHERE C.xusertype = T.xusertype

AND T.name = ''smalldatetime''

AND C.id = OBJECT_ID(N''?'')

OPEN CUR

DECLARE @s nvarchar(4000)

FETCH CUR INTO @s

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT(@s)

EXEC(@s)

FETCH CUR INTO @s

END

CLOSE CUR

DEALLOCATE CUR

',

@whereand = N'

AND EXISTS(

SELECT * FROM syscolumns C, systypes T

WHERE C.xusertype = T.xusertype

AND T.name = ''smalldatetime''

AND C.id = O.id)

'