Database | Posted by locke 2007.03.14 10:34

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

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개 행 적용됨)

(1개 행 적용됨)

(1개 행 적용됨)

(1개 행 적용됨)

(90개 행 적용됨)
alter table [test] drop column [test] 실행
TAG