新建一个数据库 (a),将数据全部导出到 a , 然后用你的方法修改数据表的结构, 然后再将 a 的数据倒入到修改好的数据库。
--这样呢!你先测试一下 CREATE TABLE TEST1(field1 nvarchar(20),field2 nvarchar(20))SP_HELP 'TEST1'EXEC sp_configure 'allow updates',1 RECONFIGURE WITH OVERRIDE GO UPDATE syscolumns SET xusertype = '167',XTYPE='167' WHERE id = OBJECT_ID('TEST1') AND XTYPE='231' GO EXEC sp_configure 'allow updates',0 RECONFIGURE WITH OVERRIDE SP_HELP 'TEST1'
167--Varchar类型 231--nvarchar类型 通过Systypes表中得到
to liangpei2008(我爱世界杯) : 爽!虽然我不明白啥意思,但是完全符合我的要求!谢谢!
EXEC sp_configure 'allow updates',1 RECONFIGURE WITH OVERRIDE GO DECLARE tables_cursor CURSOR FOR SELECT name FROM sysobjects WHERE type = 'U' OPEN tables_cursor DECLARE @tablename varchar(128) FETCH NEXT FROM tables_cursor INTO @tablename WHILE (@@FETCH_STATUS <> -1) BEGIN exec( 'UPDATE syscolumns SET xusertype = 167,XTYPE=167 WHERE id = OBJECT_ID('''+@tablename+''') AND XTYPE=231') FETCH NEXT FROM tables_cursor INTO @tablename END CLOSE tables_cursor DEALLOCATE tables_cursor GO EXEC sp_configure 'allow updates',0 RECONFIGURE WITH OVERRIDE
a 的数据倒入到修改好的数据库。
CREATE TABLE TEST1(field1 nvarchar(20),field2 nvarchar(20))SP_HELP 'TEST1'EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
GO
UPDATE syscolumns
SET xusertype = '167',XTYPE='167'
WHERE id = OBJECT_ID('TEST1') AND XTYPE='231'
GO
EXEC sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
SP_HELP 'TEST1'
231--nvarchar类型
通过Systypes表中得到
爽!虽然我不明白啥意思,但是完全符合我的要求!谢谢!
RECONFIGURE WITH OVERRIDE
GO
DECLARE tables_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tables_cursor
DECLARE @tablename varchar(128)
FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
exec( 'UPDATE syscolumns
SET xusertype = 167,XTYPE=167
WHERE id = OBJECT_ID('''+@tablename+''') AND XTYPE=231')
FETCH NEXT FROM tables_cursor INTO @tablename
END
CLOSE tables_cursor
DEALLOCATE tables_cursor
GO
EXEC sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE