Geek 블로그 :: 열에 Null값만 있을경우 해당열 삭제

열에 Null값만 있을경우 해당열 삭제

SQL 2007/03/14 10:34

if object_id('test') is not null
drop table test
create table test (num int identity,test int)
insert test values(null)
insert test values(null)
insert test values(null)
insert test values(null)

-------------------------------------------------
-- 여기부터

if object_id('tempdb..#temp') is not null
drop table #temp
select identity(int, 1,1) seq,a.name [table],b.name [column] into #temp
from sysobjects a,syscolumns b where a.id = b.id and a.xtype = 'U'
order by a.name

declare @i int,@sql nvarchar(1000),@count int
set @i = 1
while exists(select * from #temp where seq = @i)
begin
   select @sql = N'select @count=sum(case when '+[column]+' is null then 0 else 1 end) from [' + [table] + ']'
   from #temp
   where seq = @i

   exec sp_executesql @sql,N'@count int output',@count output
   if @count = 0
begin

select @sql = N'alter table [' + [table] + '] drop column [' + [column] + ']'
from #temp
where seq = @i

print @sql + ' 실행'

exec(@sql)

end
set @i = @i + 1
end
if object_id('tempdb..#temp') is not null
drop table #temp
if object_id('test') is not null
drop table test

-------------------------------------------------
--결과

(1개 행 적용됨)

(90개 행 적용됨)

alter table [test] drop column [test] 실행