delete from tb where col1 is null or col1=0 or col2 is null or col2=0 .....
应该用alter table 吧.....
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
or col1=0
or col2 is null
or col2=0
.....
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