zjcxc(邹建)的Blog - SQL Server

引用或者转载本BLOG的文章请注明原作者和出处,并保留原文章中的版权信息。谢谢!...

将当前数据库中所有表的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)

'

阅读更多
个人分类: T-SQL常用小脚本
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭