各位论坛的朋友们，小弟的SQL数据库里面大概有2百多张表，每张表都有很多VARCHAR的字段，如何做，才能将数据库里面的所有表，字段类型为VARCHAR的，全部一次性改为NVARCHAR呢？另外，原来的长度是多少，改成NVARCHAR之后也要设置为多少，例如：原来的类型是 VARCHAR 30,转换之后的类型为：NVARCHAR 30,请问这样可以实现吗？因为手工来一个一个的改，实在是太累人的，诚心向各位朋友求教，谢谢！
------解决方案--------------------
- SQL code
-- xtype = '167' 字段類型為 varchar 可用 select * from systypes 查看Declare curAlterInfo Cursor For Select a.Name AlterFieldName, a.Length, a.IsNullable, b.Name AlterTableName From SysColumns a, SysObjects b Where a.id = b.id and a.xType = '167' and b.xType = 'u' and a.cdefault = 0declare @AlterFieldName nvarchar(50), -- 修改的字段 @AlterTableName nvarchar(50), -- 修改的表名 @Length int, -- 字段以長度 @IsNullable bit, -- 字段是否允許為空 @PkName nvarchar(50), -- 主鍵名 @PkFieldName nvarchar(500), --主鍵字段名 @TmpFieldName nvarchar(50), @TmpTableName nvarchar(50), @Sql nvarchar(500)Set NoCount OnBegin Tranopen curAlterInfoFetch curAlterInfo Into @AlterFieldName, @Length, @IsNullable, @AlterTableNameWhile @@Fetch_Status=0Begin print @AlterTableName --檢查修改的表是否有主鍵 If Exists(Select Name From SysObjects Where xType = 'PK' and Parent_Obj = (Select id From SysObjects Where Name = @AlterTableName)) Begin Set @TmpTableName = @AlterTableName -- 取得主鍵名 Select @PkName = Name From SysObjects Where xType = 'PK' and Parent_Obj = (Select id From SysObjects Where Name = @AlterTableName) Set @PkFieldName = '' -- 主鍵字段 Declare curPkFieldName Cursor For Select b.Name From SysIndexKeys a, SysColumns b Where a.id = (Select id From SysIndexes Where Name = @PkName) and a.indid = 1 and a.colid = b.colid and a.id = b.id -- 取得所有的主鍵字段 Open curPkFieldName Fetch curPkFieldName Into @TmpFieldName While @@fetch_status = 0 Begin Set @PkFieldName = @PkFieldName + @TmpFieldName + ',' Fetch curPkFieldName Into @TmpFieldName End Close curPkFieldName Deallocate curPkFieldName -- 刪除舊主鍵 Set @Sql = 'ALTER TABLE '+ @AlterTableName + ' DROP CONSTRAINT ' + @PkName Print @Sql Exec(@Sql) end -- 修改字段 Set @Sql = 'ALTER TABLE ' + @AlterTableName + ' ALTER COLUMN ' + @AlterFieldName + ' NVARCHAR( ' + CAST(@Length AS NVARCHAR) + ')' -- 是否允許為空 if @IsNullable = 0 Set @Sql = @Sql + ' NOT NULL' Print @sql Exec(@sql) Fetch curAlterInfo Into @AlterFieldName, @Length, @IsNullable, @AlterTableName -- 創建主鍵 If (@AlterTableName <> @TmpTableName or @@fetch_status <> 0) and @PkFieldName <> '' Begin Set @PkFieldName = Left(@PkFieldName, Len(@PkFieldName) - 1) Set @Sql = ' ALTER TABLE ' + @TmpTableName + ' ADD CONSTRAINT ' + @PkName + ' PRIMARY KEY CLUSTERED(' + @PkFieldName + ') ON [PRIMARY]' Print @Sql Exec(@Sql) print '-----------------------------' Set @PkFieldName = '' EndEndClose curAlterInfoDeallocate curAlterInfoIf @@Error > 0 Rollback TranElse Commit TranSet NoCount Off
------解决方案--------------------
- SQL code
有什么哪里不明白的指出来~~
------解决方案--------------------
------解决方案--------------------