create table Table1(col nvarchar(2),col2 nvarchar(2)) go declare @s nvarchar(4000) set @s='' select @s=@s+' update Table1 set '+quotename(Name)+'=null where RTRIM(LTRIM('+Name+'))='''''+char(10) from syscolumns where ID=object_id('Table1') order by colid asc print @s 生成的语句如下: /* update Table1 set [col]=null where RTRIM(LTRIM(col))='' update Table1 set [col2]=null where RTRIM(LTRIM(col2))=''*/exec (@s)
use test go declare t_cursor cursor for select ID from sysobjects where objectProperty(ID,N'IsUserTable')=1 declare @Object_ID int open t_cursor fetch next from t_cursor into @Object_ID while @@fetch_status=0 begin declare @s nvarchar(4000) set @s='' select @s=@s+' update Table1 set '+quotename(Name)+'=null where RTRIM(LTRIM('+Name+'))='''''+char(10) from syscolumns where ID=@Object_ID order by colid asc exec (@s) end close t_cursor deallocate t_cursor
/* 用动态SQL,循环所有表 */UPDATE Table1 SET Field1=nullif(isnull(Field1,''),'') Field2=nullif(isnull(Field2,''),'') Field3=nullif(isnull(Field3,''),'') ... FieldX=nullif(isnull(FieldX,''),'')
use test go declare t_cursor cursor for select ID from sysobjects where objectProperty(ID,N'IsUserTable')=1 declare @Object_ID int open t_cursor fetch next from t_cursor into @Object_ID while @@fetch_status=0 begin declare @s nvarchar(4000) set @s='' select @s=@s+' update Table1 set '+quotename(Name)+'=null where RTRIM(LTRIM('+Name+'))='''''+char(10) from syscolumns where ID=@Object_ID order by colid asc exec (@s) fetch next from t_cursor into @Object_ID --漏少循环 end close t_cursor deallocate t_cursor
declare @tname varchar(100) declare @cname varchar(100) declare tc cursor for select b.name tname,a.name cname from syscolumns a,sysobjects b where a.id = b.id and b.type = 'U' open tcfetch next from tc into @tname,@cnamewhile @@fetch_status=0 begin
exec( 'update '+@tname+' set '+@cname+' = null where RTRIM(LTRIM('+@cname+'))=''''' ) fetch next from tc into @tname,@cname endclose tc deallocate tc
create proc sp_setnull as begin declare @tname varchar(100) declare @cname varchar(100) declare tc cursor for select b.name tname,a.name cname from syscolumns a,sysobjects b where a.id = b.id and b.type = 'U' open tc
fetch next from tc into @tname,@cname
while @@fetch_status=0 begin
exec( 'update '+@tname+' set '+@cname+' = null where RTRIM(LTRIM('+@cname+'))=''''' ) fetch next from tc into @tname,@cname end
go
declare @s nvarchar(4000)
set @s=''
select @s=@s+' update Table1 set '+quotename(Name)+'=null where RTRIM(LTRIM('+Name+'))='''''+char(10)
from syscolumns
where ID=object_id('Table1')
order by colid asc
print @s
生成的语句如下:
/*
update Table1 set [col]=null where RTRIM(LTRIM(col))=''
update Table1 set [col2]=null where RTRIM(LTRIM(col2))=''*/exec (@s)
go
declare t_cursor cursor for
select ID from sysobjects where objectProperty(ID,N'IsUserTable')=1
declare @Object_ID int
open t_cursor
fetch next from t_cursor into @Object_ID
while @@fetch_status=0
begin
declare @s nvarchar(4000)
set @s=''
select @s=@s+' update Table1 set '+quotename(Name)+'=null where RTRIM(LTRIM('+Name+'))='''''+char(10)
from syscolumns
where ID=@Object_ID
order by colid asc
exec (@s)
end
close t_cursor
deallocate t_cursor
用动态SQL,循环所有表
*/UPDATE Table1 SET
Field1=nullif(isnull(Field1,''),'')
Field2=nullif(isnull(Field2,''),'')
Field3=nullif(isnull(Field3,''),'')
...
FieldX=nullif(isnull(FieldX,''),'')
go
declare t_cursor cursor for
select ID from sysobjects where objectProperty(ID,N'IsUserTable')=1
declare @Object_ID int
open t_cursor
fetch next from t_cursor into @Object_ID
while @@fetch_status=0
begin
declare @s nvarchar(4000)
set @s=''
select @s=@s+' update Table1 set '+quotename(Name)+'=null where RTRIM(LTRIM('+Name+'))='''''+char(10)
from syscolumns
where ID=@Object_ID
order by colid asc
exec (@s)
fetch next from t_cursor into @Object_ID --漏少循环
end
close t_cursor
deallocate t_cursor
declare @cname varchar(100)
declare tc cursor for select b.name tname,a.name cname from syscolumns a,sysobjects b
where a.id = b.id and b.type = 'U'
open tcfetch next from tc into @tname,@cnamewhile @@fetch_status=0
begin
exec( 'update '+@tname+' set '+@cname+' = null where RTRIM(LTRIM('+@cname+'))=''''' )
fetch next from tc into @tname,@cname
endclose tc
deallocate tc
是否允许NULL,查联机丛书syscolumns表的status字段。懒得写了,参照楼上的
create proc sp_setnull
as
begin
declare @tname varchar(100)
declare @cname varchar(100)
declare tc cursor for select b.name tname,a.name cname from syscolumns a,sysobjects b
where a.id = b.id and b.type = 'U'
open tc
fetch next from tc into @tname,@cname
while @@fetch_status=0
begin
exec( 'update '+@tname+' set '+@cname+' = null where RTRIM(LTRIM('+@cname+'))=''''' )
fetch next from tc into @tname,@cname
end
close tc
deallocate tc
end