【SQL Server 学习系列】-- SQL查询数据库表字段值不为空或Null的所有列

 1 declare @TableName nvarchar(200)
 2 set @TableName = 'Agency'   -- 表名
 3 
 4 declare @querySql nvarchar(max) 
 5 set @querySql = 'select '
 6 
 7 declare @col varchar(50)   
 8 declare My_Cursor cursor
 9 for(select name from syscolumns where  id = (select max(id) from sysobjects where xtype = 'u' and name = '' + @TableName + '' ) ) 
10 open My_Cursor;
11 fetch next from My_Cursor into @col; 
12 while @@FETCH_STATUS = 0  
13     begin  
14         declare @sql varchar(2000)
15         declare @TempTable TABLE(NAME NVARCHAR(2000))
16         delete from @TempTable
17         set @sql='SELECT '+ @col +' FROM '+@TableName+''
18 
19         insert @TempTable 
20         exec(@sql)
21         
22         -- if not exists (select NAME from @TempTable where len(NAME)>0 group by NAME)    --查询字段值全为空或NULL的列
23         if exists (select count(1) from @TempTable where isnull(NAME, '')<> '') -- 查询字段值不全为空或NULL的列
24             begin
25                 set @querySql = @querySql + @col + ', '
26             end 
27        
28         fetch next from My_Cursor into @col; 
29     end  
30 close My_Cursor; --关闭游标  
31 deallocate My_Cursor; --释放游标   
32 
33 if @querySql <> 'select '
34     begin
35         set @querySql = left(@querySql, len(@querySql) - 1)
36         set @querySql = @querySql + ' from ' + @TableName + ''
37         print (@querySql)
38 
39         exec(@querySql)
40     end