今天用友软件免费下载网站要给大家分享一段SQL代码，主要用于检测用友软件的数据库是否有特殊字符？例如说TAB键，空格，特殊符号等，可直接用于检测，废话不多说，直接上源SQL代码，大家自己尝试使用吧！
-----检测表中特殊字符，如tab、换行符、回车符---
------将wa_gzdata替换成要检查的表名即可 ----
------将有特殊字符的列存放在tmp_aa临时表中
go
if exists(select * from sysobjects where name='tmp_aa' and xtype='U')
drop table tmp_aa
go
CREATE TABLE tmp_aa( a varchar(20), b varchar(20))
go
declare @tablename varchar (50)
set @tablename='wa_gzdata'
declare my_cur cursor for
select name from syscolumns where id = object_id(@tablename) and xtype in (167,175,239,99)--nvarchar,varchar,char,nchar型
open my_cur
declare @col_ls nvarchar(50)
fetch next from my_cur into @col_ls
while @@fetch_status=0
BEGIN
declare @sql1 varchar(1000),@ls_where varchar(100),@ls_set varchar(100)
declare @sql2 varchar(1000)
declare @sql3 varchar(1000)
--检测tab键
select @sql1 = 'select '+@col_ls+' from '+@tablename +' where CHARINDEX(CHAR(9),'+@col_ls+')>0'
set @ls_where=' where CHARINDEX(CHAR(9),'+@col_ls+')>0'
select @sql1 = 'select '+@col_ls+' from '+@tablename +@ls_where
EXEC(@sql1)
--------替换tab
if @@ROWCOUNT>0
begin
insert into tmp_aa (a,b) values (@col_ls,'tab键')
set @sql1='update'+@tablename +' set ' +@col_ls+'='
set @ls_set='REPLACE('+@col_ls+',char(9),'+''''+''''+')'
select @sql2= 'update '+ @tablename+ ' set ' +@col_ls+'='+@ls_set+@ls_where
--print @sql2
exec(@sql2)
end
set @sql1=''
--char（10）检测换行
select @sql1 = 'select '+@col_ls+' from '+@tablename +' where CHARINDEX(CHAR(10),'+@col_ls+')>0'
set @ls_where=' where CHARINDEX(CHAR(10),'+@col_ls+')>0'
select @sql1 = 'select '+@col_ls+' from '+@tablename +@ls_where
--print @sql1
EXEC(@sql1)
if @@ROWCOUNT>0
begin
insert into tmp_aa (a,b) values (@col_ls,'换行符')
set @sql1='update'+@tablename +' set ' +@col_ls+'='
set @ls_set='REPLACE('+@col_ls+',char(10),'+''''+''''+')'
select @sql2= 'update '+ @tablename+ ' set ' +@col_ls+'='+@ls_set+@ls_where
-- print @sql2
exec(@sql2)
end
set @sql1=''
--- --char（13）检测回车键
select @sql1 = 'select '+@col_ls+' from '+@tablename +' where CHARINDEX(CHAR(10),'+@col_ls+')>0'
set @ls_where=' where CHARINDEX(CHAR(10),'+@col_ls+')>0'
select @sql1 = 'select '+@col_ls+' from '+@tablename +@ls_where
--print @sql1
EXEC(@sql1)
if @@ROWCOUNT>0
begin
insert into tmp_aa (a,b) values (@col_ls,'回车键')
set @sql1='update'+@tablename +' set ' +@col_ls+'='
set @ls_set='REPLACE('+@col_ls+',char(13),'+''''+''''+')'
select @sql2= 'update '+ @tablename+ ' set ' +@col_ls+'='+@ls_set+@ls_where
--print @sql2
exec(@sql2)
end
fetch next from my_cur into @col_ls
END
close my_cur
DEALLOCATE my_cur
