各位论坛的朋友们，小弟的SQL数据库里面大概有2百多张表，每张表都有很多VARCHAR的字段，如何做，才能将数据库里面的所有表，字段类型为VARCHAR的，全部一次性改为NVARCHAR呢？另外，原来的长度是多少，改成NVARCHAR之后也要设置为多少，例如：原来的类型是 VARCHAR 30,转换之后的类型为：NVARCHAR 30,请问这样可以实现吗？因为手工来一个一个的改，实在是太累人的，诚心向各位朋友求教，谢谢！
调试欢乐多
-- 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
转一个邹老大的
2楼 zjcxc (邹建) 回复于 2004-08-11 21:13:16 得分 100
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_set]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_set]
GO
/*--将所有的表中,数值类型由char,varchar改为nchar,nvarchar
--*/
/*--调用示例:
exec p_set
--*/
--修改的存储过程
create procedure p_set
as
declare tb cursor for
SELECT sql='alter table ['+d.name
+'] alter column ['+a.name+'] n'
+b.name+'('+cast(a.length*2 as varchar)+')'
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
where
b.name in('char','varchar')
and
not exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) --主键不能修改
order by d.name,a.name
declare @sql varchar(1000)
open tb
fetch next from tb into @sql
while @@fetch_status = 0
begin
exec(@sql)
fetch next from tb into @sql
end
close tb
deallocate tb
go
EXEC sp_MSforeachtable 'declare @sql varchar(max);set @sql=''''
select @sql=@sql+'' alter table ? alter column '' + name+'' nvarchar(''+ltrim(length)+'');'' from syscolumns where id=object_id(''?'') and xtype=167;
exec(@sql)'
go
--存储过程中的varchar变量换成nvarcharif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_convert]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_convert]
GO--创建一个函数,替换指定字符串中的varchar为nvarchar
create function f_convert(@text nvarchar(4000))
returns varchar(8000)
as
begin
declare @i1 int,@i2 int,@i3 varchar(20),@re varchar(8000)
select @i1=patindex('% varchar(%)%',@text)
,@i2=charindex(')',@text,@i1)
,@i3=substring(@text,@i1+9,@i2-@i1-9)
,@re='' while @i1>0 and @i2>0
begin
if isnumeric(@i3)=1
if @i3>4000
select @re=@re+left(@text,@i1)+'nvarchar(4000)'
else
select @re=@re+left(@text,@i1)+'nvarchar('
+substring(@text,@i1+9,@i2-@i1-8)
else
select @re=@re+left(@text,@i2)
select @text=stuff(@text,1,@i2,'')
,@i1=patindex('% varchar(%)%',@text)
,@i2=charindex(')',@text,@i1)
,@i3=substring(@text,@i1+9,@i2-@i1-9)
end
return(@re+@text)
end
go--调用函数实现转换处理:declare @id int,@name sysname
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)declare tb cursor local for
select top 1 id,'['+replace(name,']',']]')+']' from sysobjects a
where xtype='P' and status>=0 and exists(
select 1 from syscomments
where id=a.id and charindex('varchar(',text)>0)
open tb
fetch tb into @id,@name
while @@fetch_status=0
begin
select @s1='',@s2='',@s3='' select @s1=@s1+',@'+cast(colid as varchar)+' varchar(8000)'
,@s2=@s2+';select @'+cast(colid as varchar)
+'=f_convert(text) from syscomments where id='
+cast(@id as varchar)+' and colid='+cast(colid as varchar)
,@s3=@s3+'+@'+cast(colid as varchar)
from syscomments
where id=@id
order by colid select @s1=stuff(@s1,1,1,'')
,@s2=stuff(@s2,1,1,'')
,@s3=stuff(@s3,1,1,'')
exec('declare '+@s1+'
'+@s2+'
drop proc '+@name+'
exec('+@s3+')
')
fetch tb into @id,@name
end
close tb
deallocate tb