查询数据库从来没有被用过的列

原创：西湖浪子
时间：2008-03-05

Create table #AllTableNullColumn(Tablename varchar(50) null,NullColumn varchar(50) null)
Drop table #AllTableNullColumn
Declare @tablelist varchar(8000)

set @tablelist=''
select top 5 @tablelist=@tablelist+name+','from sysobjects where xtype='U' order by name
--print @tablelist

Declare @f int , @g int
declare @tablename varchar(50)
Select @g=len(@tablelist)
select @f=charindex(',',@tablelist)

While (@f<>0)
BEGIN
Select @tablename=LTrim(SubString(@tablelist,0,@f))
print @tablename

 declare @columnlist varchar(8000)
 set @columnlist=''
 SELECT @columnlist=@columnlist+syscolumns.name+',' FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id INNER JOIN systypes ON syscolumns.xtype = systypes.xtype WHERE sysobjects.name = @tablename AND sysobjects.xtype = 'U'  order by syscolumns.id

 Declare @cf int , @cg int
 declare @columnname varchar(500)
 Select @cg=len(@columnlist)
 select @cf=charindex(',',@columnlist)

 While (@cf<>0)
 BEGIN
 Select @columnname=LTrim(SubString(@columnlist,0,@cf))
 declare @sqlstr varchar(2000)
 
 set @sqlstr='select top 1 * from '+@tablename +' where '+@columnname+' is not null'
 --print @sqlstr
 
 exec(@sqlstr)

 If @@rowcount=0
 begin
 print @columnname
 end
 
 select @columnlist=SubString(@columnlist,@cf+1,@cg)
 select @cf=charindex(',',@columnlist)
 END

 

select @tablelist=SubString(@tablelist,@f+1,@g)
select @f=charindex(',',@tablelist)
END