本文介绍了如何使用SQL语句批量删除MySQL数据库中存在空值的列。通过声明游标并遍历表格，检查每列是否存在空值，如果为空则删除该列。文中提到了原SQL脚本的一个问题，并给出了修正后的解决方案。
declare tab_cur cursor for select name from dbo.sysobjects where xtype='U' and name<>'dtproperties'
declare @tab_name varchar(50)
declare @sql varchar(100)
open tab_cur
fetch next from tab_cur into @tab_name
while @@fetch_status=0
begin
fetch next from tab_cur into @tab_name
declare col_cur cursor for select a.name from dbo.syscolumns a, .dbo.sysobjects b where a.id=b.id and b.name=@tab_name
declare @col varchar(30)
begin
open col_cur
fetch next from col_cur into @col
while @@fetch_status=0
begin
set @sql='select * from dbo.'+@tab_name+' where '+@col+'<>'''''
execute(@sql)
if @@rowcount=0
begin
set @sql=' alter table dbo.'+@tab_name+' drop column '+@col
execute(@sql)
end
fetch next from col_cur into @col
end
close col_cur
deallocate col_cur
end
close tab_cur
deallocate tab_cur
end
--------------------------------------------------------------------------------------------------------------------------------------------------------
是这个地方的问题set @sql='select * from dbo.'+@tab_name+' where '+@col+'<>'''''
改成:set @sql='select * from dbo.'+@tab_name+' where '+@col+' is not null'
3742
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
