数据库里面大概有2百多张表，每张表都有很多VARCHAR的字段，如何做，才能将数据库里面的所有表，字段类型为VARCHAR的，全部一次性改为NVARCHAR呢？
由于需要考虑到主键外键 约束的问题，所以这个问题变得复杂了许多，现在提供可行的一种方法：
-- 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
= 0
declare @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 On
Begin Tran
open curAlterInfo
Fetch curAlterInfo Into @AlterFieldName, @Length,
@IsNullable, @AlterTableName
While @@Fetch_Status=0
Begin
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 = ''
End
End
Close curAlterInfo
Deallocate curAlterInfo
If @@Error > 0
Rollback Tran
Else
Commit Tran
Set NoCount Off
