WEB开发网
开发学院网络安全黑客技术 将当前数据库中所有表的smalldatetime 列改为nvar... 阅读

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

 2008-11-08 16:50:30 来源:WEB开发网   
核心提示:-- 将当前数据库中, 所有表的smalldatetime 列改为nvarchar(20)-- 如果列上有索引/默认值之类的依赖项, 则无法修改EXEC sp_msforeachtable@command1 = N'DECLARE CUR CURSOR LOCALFORSELECTN''ALTE

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

Tags:当前 数据库 所有

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接