declare @sql varchar(8000) set @sql='' select @sql=@sql+'update ['+b.name+'] set ['+a.name+']='''' where ['+a.name+'] is NULL;' from syscolumns a,sysobjects b,systypes c where a.id=b.id and b.xtype='U' and a.xtype=c.xtype and c.name in('text') exec(@sql)
if exists(select 1 from sysobjects where xtype='p' and name='update_null') drop proc update_null go /* 功能:处理表test字段中的NULL值,用''替换 作者:WGS 创建时间:2006-06-14 调用事例: 1。对一个表操作:update 'tb1' 2。对整个数据库操作:sp_msforeachtable "update '?'" */ create proc update_null(@tbname nvarchar(50)) as declare @fld varchar(20),@ftype varchar(30),@fid int,@sql varchar(300) declare cur cursor for select a.name,b.name,a.xusertype from syscolumns a,systypes b where a.xusertype=b.xusertype and a.id=object_id(@tbname) order by b.xusertype open cur fetch next from cur into @fld,@ftype,@fid while @@fetch_status=0 begin if @ftype in('text','ntext') exec('update '+@tbname+' set '+@fld+'='''' where '+@fld+' is null') fetch next from cur into @fld,@ftype,@fid end close cur deallocate curgo --
--try if exists(select 1 from sysobjects where xtype='p' and name='update_null') drop proc update_null go /* 功能:处理表test字段中的NULL值,用''替换 作者:WGS 创建时间:2006-06-14 调用事例: 1。对一个表操作:update_null 'tb1' 2。对整个数据库操作:sp_msforeachtable "update_null '?'" */ create proc update_null(@tbname nvarchar(50)) as declare @fld varchar(20),@ftype varchar(30),@fid int,@sql varchar(300) declare cur cursor for select a.name,b.name,a.xusertype from syscolumns a,systypes b where a.xusertype=b.xusertype and a.id=object_id(@tbname) order by b.xusertype open cur fetch next from cur into @fld,@ftype,@fid while @@fetch_status=0 begin if @ftype in('text','ntext') exec('update '+@tbname+' set '+@fld+'='''' where '+@fld+' is null') fetch next from cur into @fld,@ftype,@fid end close cur deallocate curgo
--try if exists(select 1 from sysobjects where xtype='p' and name='update_null') drop proc update_null go /* 功能:处理表中"text"和"ntext"类型字段中的NULL值,用''替换 作者:WGS 创建时间:2006-06-14 调用事例: 1。对一个表操作:update_null 'tb1' 2。对整个数据库操作:sp_msforeachtable "update_null '?'" */ create proc update_null(@tbname nvarchar(50)) as declare @fld varchar(20),@ftype varchar(30),@fid int,@sql varchar(300) declare cur cursor for select a.name,b.name,a.xusertype from syscolumns a,systypes b where a.xusertype=b.xusertype and a.id=object_id(@tbname) order by b.xusertype open cur fetch next from cur into @fld,@ftype,@fid while @@fetch_status=0 begin if @ftype in('text','ntext') exec('update '+@tbname+' set '+@fld+'='''' where '+@fld+' is null') fetch next from cur into @fld,@ftype,@fid end close cur deallocate curgo
from tablename
where textcol is null
set @sql=''
select @sql=@sql+'update ['+b.name+'] set ['+a.name+']='''' where ['+a.name+'] is NULL;' from syscolumns a,sysobjects b,systypes c where a.id=b.id and b.xtype='U' and a.xtype=c.xtype and c.name in('text')
exec(@sql)
drop proc update_null
go
/*
功能:处理表test字段中的NULL值,用''替换
作者:WGS
创建时间:2006-06-14
调用事例:
1。对一个表操作:update 'tb1'
2。对整个数据库操作:sp_msforeachtable "update '?'"
*/
create proc update_null(@tbname nvarchar(50))
as
declare @fld varchar(20),@ftype varchar(30),@fid int,@sql varchar(300)
declare cur cursor for
select a.name,b.name,a.xusertype from syscolumns a,systypes b
where a.xusertype=b.xusertype and a.id=object_id(@tbname) order by b.xusertype
open cur
fetch next from cur into @fld,@ftype,@fid
while @@fetch_status=0
begin
if @ftype in('text','ntext')
exec('update '+@tbname+' set '+@fld+'='''' where '+@fld+' is null')
fetch next from cur into @fld,@ftype,@fid
end
close cur
deallocate curgo
--
if exists(select 1 from sysobjects where xtype='p' and name='update_null')
drop proc update_null
go
/*
功能:处理表test字段中的NULL值,用''替换
作者:WGS
创建时间:2006-06-14
调用事例:
1。对一个表操作:update_null 'tb1'
2。对整个数据库操作:sp_msforeachtable "update_null '?'"
*/
create proc update_null(@tbname nvarchar(50))
as
declare @fld varchar(20),@ftype varchar(30),@fid int,@sql varchar(300)
declare cur cursor for
select a.name,b.name,a.xusertype from syscolumns a,systypes b
where a.xusertype=b.xusertype and a.id=object_id(@tbname) order by b.xusertype
open cur
fetch next from cur into @fld,@ftype,@fid
while @@fetch_status=0
begin
if @ftype in('text','ntext')
exec('update '+@tbname+' set '+@fld+'='''' where '+@fld+' is null')
fetch next from cur into @fld,@ftype,@fid
end
close cur
deallocate curgo
语句已终止。
if exists(select 1 from sysobjects where xtype='p' and name='update_null')
drop proc update_null
go
/*
功能:处理表中"text"和"ntext"类型字段中的NULL值,用''替换
作者:WGS
创建时间:2006-06-14
调用事例:
1。对一个表操作:update_null 'tb1'
2。对整个数据库操作:sp_msforeachtable "update_null '?'"
*/
create proc update_null(@tbname nvarchar(50))
as
declare @fld varchar(20),@ftype varchar(30),@fid int,@sql varchar(300)
declare cur cursor for
select a.name,b.name,a.xusertype from syscolumns a,systypes b
where a.xusertype=b.xusertype and a.id=object_id(@tbname) order by b.xusertype
open cur
fetch next from cur into @fld,@ftype,@fid
while @@fetch_status=0
begin
if @ftype in('text','ntext')
exec('update '+@tbname+' set '+@fld+'='''' where '+@fld+' is null')
fetch next from cur into @fld,@ftype,@fid
end
close cur
deallocate curgo