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'