批量去除SQL里面的回车换行等数据
Declare @columnsName varchar(8000);--列名称
Declare @columnsType varchar(50);--列类型
Declare @columnsLen varchar(50);--列长度
Declare @SQLText varchar(5000);--SQL 文
declare tablecursor cursor FOR
select tc.name as 列名称,tt.name as 列类型,
tt.length as 长度 from syscolumns tc, systypes tt
where tc.xtype=tt.xtype and
tc.id=object_id('库名称..表名称')
order by tc.name;
open tablecursor;
FETCH NEXT FROM tablecursor INTO @columnsName,@columnsType,@columnsLen;
WHILE @@FETCH_STATUS = 0
begin
if @columnsType='varchar'
begin
set @SQLText='update 库名称..表名称 set '+@columnsName+
'=Replace(Replace(Replace(Replace(Replace('+@columnsName+',Char(9),''''),Char(10),''''),Char(13),''''),''"'',''''),'' '','''');';
--exec sp_executesql @SQLText;
print @SQLText;
end;
FETCH NEXT FROM tablecursor INTO @columnsName,@columnsType,@columnsLen;
end;
CLOSE tablecursor;
DEALLOCATE tablecursor;
