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

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

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

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)

'