257.存储过程找那个的varchar表里换成nvchar

古城微笑少年丶 2023-12-27 06:29 5阅读 0赞

--存储过程中的varchar变量换成nvarchar
    
    if 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