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

declare @TableName nvarchar(200)
set @TableName = 'Agency'   -- 表名

declare @querySql nvarchar(max) 
set @querySql = 'select '

declare @col varchar(50)   
declare My_Cursor cursor
for(select name from syscolumns where  id = (select max(id) from sysobjects where xtype = 'u' and name = '' + @TableName + '' ) ) 
open My_Cursor;
fetch next from My_Cursor into @col; 
while @@FETCH_STATUS = 0  
    begin  
        declare @sql varchar(2000)
        declare @TempTable TABLE(NAME NVARCHAR(2000))
        delete from @TempTable
        set @sql='SELECT '+ @col +' FROM '+@TableName+''

        insert @TempTable 
        exec(@sql)
        
        -- if not exists (select NAME from @TempTable where len(NAME)>0 group by NAME)    --查询字段值全为空或NULL的列
        if exists (select count(1) from @TempTable where isnull(NAME, '')<> '') -- 查询字段值不全为空或NULL的列
            begin
                set @querySql = @querySql + @col + ', '
            end 
       
        fetch next from My_Cursor into @col; 
    end  
close My_Cursor; --关闭游标  
deallocate My_Cursor; --释放游标   

if @querySql <> 'select '
    begin
        set @querySql = left(@querySql, len(@querySql) - 1)
        set @querySql = @querySql + ' from ' + @TableName + ''
        print (@querySql)

        exec(@querySql)
    end