none
SELECT * to select only non empty columns

    Question

  • Hi,
    I have a very simple question, I want to use SELECT * to select the entire table contents except those empty columns. How should i do this?
    Thanks,
    • Moved by Darren GosbellMVP Thursday, June 11, 2009 1:05 AM This is a T-SQL question (From:SQL Server Analysis Services)
    Wednesday, June 10, 2009 3:10 PM

Answers

  • While I do agree this will be a nasty query, I wanted to point out that you do not have to check every column. You will only need to check the columns that have Is_Nullable set to true.  All other columns should be included because null are not allowed. This will help to reduce the number of checks required. 

    I still want to know why we are wanting to do this type of query, as I have never seen such a request.

    Here is a sample of the code.

    DECLARE @Table VARCHAR(100),
    		@ColumnName VARCHAR(100),
    		@Is_Nullable BIT,
    		@Cols VARCHAR(MAX),
    		@sql NVARCHAR(500),
    		@Rtn INT
    
    DECLARE curGetCols CURSOR FAST_FORWARD FOR
    SELECT t.NAME, c.NAME, c.is_nullable
    FROM sys.tables t 
    INNER JOIN sys.columns c 
    	ON t.Object_ID = c.Object_ID
    WHERE t.Name = 'CafeContractAdjustment'
    
    OPEN curGetCols
    FETCH NEXT FROM curGetCols INTO @Table,@ColumnName, @Is_Nullable
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	IF @Is_Nullable = 0
    	BEGIN
    		SET @Cols = COALESCE(@Cols + ',','') + @ColumnName
    	END
    	ELSE
    	BEGIN
    		SET @Rtn = 0
    	
    		SET @sql = N'SET @Rtn = (SELECT COUNT(*) FROM ' + QUOTENAME(@Table) + ' WHERE ' + QUOTENAME(@ColumnName) + ' IS NULL)'
    		EXEC sp_executesql @sql, N'@Rtn INT OUTPUT', @Rtn OUTPUT
    		IF @Rtn = 0
    		BEGIN
    			SET @Cols = COALESCE(@Cols + ',','') + @ColumnName
    		END
    	END
    
    	FETCH NEXT FROM curGetCols INTO @Table, @ColumnName, @Is_Nullable
    END
    
    CLOSE curGetCols
    DEALLOCATE curGetCols
    
    SET @sql = N'SELECT ' + @Cols + ' FROM ' + QUOTENAME(@Table) + '--WHERE SOMETHING'
    PRINT @sql
    --exec sp_executesql @sql

    http://jahaines.blogspot.com/
    Wednesday, June 17, 2009 6:43 PM
  • Here i have added Is_nullable clause as Adam suggested 

    
    
    declare @tableid int
    declare @tablename sysname
    
    set @tablename = N'Purchasing.Vendor'
    
    declare @columns table
    (
    columnname sysname,
    hasdata int default(0)
    )
    
    select @tableid = id
    from sys.sysobjects
    where name = PARSENAME ( @tablename,1)
    
    INSERT INTO @columns (columnname)
    select name from sys.syscolumns
    where id = @tableid and isnullable = 1
    order by colorder
    
    declare @columnname varchar(255)
    declare @rowcount int
    declare @sql nvarchar(1000)
    declare @ParmDefinition nvarchar(500);
    
    set @ParmDefinition = N'@rowcount int OUTPUT';
    
    select @columnname = min(columnname)
    from @columns
    
    while @columnname is not null
    begin
    	 set @sql = 'select @rowcount = count(*) from ' + @tablename + ' where isnull(convert(varchar(4000), '+@columnname+'), '''') != '''''
    	 print @sql
    
    	 EXECUTE sp_executesql @sql, @ParmDefinition, @rowcount=@rowcount OUTPUT;
    
    	 update @columns
    	 set hasdata = @rowcount
    	 where columnname = @columnname
    
    	 select @columnname = min(columnname)
    	 from @columns
    	 where columnname > @columnname
    end
    
    declare @column_name  nvarchar(max)
    
    select @column_name = substring ((select ', ' + name
    from sys.syscolumns
    where id = @tableid 
    and name not in ( select columnname from @columns where hasdata = 0 )
    for xml path('')),3,8000) 
    
    set @column_name = N'select ' + @column_name + ' from ' + @tablename 
    
    exec sp_executesql @column_name
    

    Thanks,
    Nimit
    Wednesday, June 17, 2009 7:23 PM

All replies

  • are you talking about MDX or SQL?

    MDX:
    SELECT NON EMPTY [Measures].members ON 0
    FROM  [Cube]

    SQL:
    don't know - also this would be the wrong forum

    greets,
    gerhard
    - www.pmOne.com -
    Wednesday, June 10, 2009 3:14 PM
  • SQL. Any ideal?
    Wednesday, June 10, 2009 3:18 PM
  • You want to SELECT all records except where all the COLUMNs are NULL?

    SELECT col1, col2, col3 FROM mytab WHERE col1 IS NULL and col2 IS NULL and col3 IS NULL;
    Thursday, June 11, 2009 11:36 AM
  • I have to use SELECT *, i dont know the col names. Any ideal?
    Wednesday, June 17, 2009 3:11 PM
  • SELECT * means all columns. There is no setting or similar which will change that fact.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, June 17, 2009 3:23 PM
  • Your pulling data without knowing the name of the COLUMNs? Why?

    There is a way to do this with dynamic SQL by using a system TABLE to get all the COLUMN names. But there's probably a better way.
    Wednesday, June 17, 2009 4:41 PM
  • You can use the following to view each column name from each table:
    SELECT table_name=sysobjects.name,
             column_name=syscolumns.name,
             datatype=systypes.name,
             length=syscolumns.length
        FROM sysobjects 
        JOIN syscolumns ON sysobjects.id = syscolumns.id
        JOIN systypes ON syscolumns.xtype=systypes.xtype
       WHERE sysobjects.xtype='U'
    ORDER BY sysobjects.name,syscolumns.colid

    Wednesday, June 17, 2009 5:03 PM
  • Your pulling data without knowing the name of the COLUMNs? Why?

    There is a way to do this with dynamic SQL by using a sytem TABLE to get all the COLUMN names. But there's probably a better way.

    Agreed, and imagine the overhead. For each column we have to run a query over all rows to see if all the rows are null, so we can exclude that column. Not only will this be a monster to build, but even a nastier monster to run!
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, June 17, 2009 5:03 PM
  • This will get you the column names for a specific table:

    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'your_table_name'

    • Edited by Jay Mazz Wednesday, June 17, 2009 5:05 PM
    Wednesday, June 17, 2009 5:04 PM
  • Here you go 

    --Test Table 
    
    create table test
    (id int,name varchar(10))
    
    insert into test 
    select 1, null 
    union all
    select 2,null 
    
    
    --to display only columns with atleast one non null data 
    declare @tableid int
    
    declare @tablename sysname
    
    set @tablename = N'test'
    
     
    
    declare @columns table
    
    (
    
    columnname sysname,
    
    hasdata int default(0)
    
    )
    
     
    
    select @tableid = id
    
    from sysobjects
    
    where name = @tablename
    
     
    
    INSERT INTO @columns (columnname)
    
    select name from syscolumns
    
    where id = @tableid
    
    order by colorder
    
     
    
    declare @columnname varchar(255)
    
    declare @rowcount int
    
    declare @sql nvarchar(1000)
    
    declare @ParmDefinition nvarchar(500);
    
    set @ParmDefinition = N'@rowcount int OUTPUT';
    
     
    
    select @columnname = min(columnname)
    
    from @columns
    
     
    
    while @columnname is not null
    
    begin
    
    set @sql = 'select @rowcount = count(*) from ' + @tablename + ' where isnull(convert(varchar(4000), '+@columnname+'), '''') != '''''
    
    print @sql
    
     
    
    EXECUTE sp_executesql @sql, @ParmDefinition, @rowcount=@rowcount OUTPUT;
    
     
    
    update @columns
    
    set hasdata = @rowcount
    
    where columnname = @columnname
    
    select @columnname = min(columnname)
    
    from @columns
    
    where columnname > @columnname
    
    end
    
     
    declare @column_name  nvarchar(max)
    select @column_name = ( select distinct  column_name = SUBSTRING ((select (', ' + columnname)
    										   from @columns b where hasdata > 0
                               FOR XML PATH( '' )
                              ), 3, 8000 )FROM @columns a)
    
    set @column_name = N'select ' + @column_name + ' from ' + @tablename 
    
    exec sp_executesql @column_name
    


    HTH

    Thanks,
    Nimit
    Wednesday, June 17, 2009 5:30 PM
  • This will show you every column (in the table you specify) that is null:
    DECLARE @col varchar(255), @cmd varchar(max)
    
    DECLARE getinfo cursor for
    SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
    WHERE t.Name = 'your_table'
    
    OPEN getinfo
    
    FETCH NEXT FROM getinfo into @col
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM your_table WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'
        EXEC(@cmd)
    
        FETCH NEXT FROM getinfo into @col
    END
    
    CLOSE getinfo
    DEALLOCATE getinfo

    Wednesday, June 17, 2009 5:31 PM
  • While I do agree this will be a nasty query, I wanted to point out that you do not have to check every column. You will only need to check the columns that have Is_Nullable set to true.  All other columns should be included because null are not allowed. This will help to reduce the number of checks required. 

    I still want to know why we are wanting to do this type of query, as I have never seen such a request.

    Here is a sample of the code.

    DECLARE @Table VARCHAR(100),
    		@ColumnName VARCHAR(100),
    		@Is_Nullable BIT,
    		@Cols VARCHAR(MAX),
    		@sql NVARCHAR(500),
    		@Rtn INT
    
    DECLARE curGetCols CURSOR FAST_FORWARD FOR
    SELECT t.NAME, c.NAME, c.is_nullable
    FROM sys.tables t 
    INNER JOIN sys.columns c 
    	ON t.Object_ID = c.Object_ID
    WHERE t.Name = 'CafeContractAdjustment'
    
    OPEN curGetCols
    FETCH NEXT FROM curGetCols INTO @Table,@ColumnName, @Is_Nullable
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	IF @Is_Nullable = 0
    	BEGIN
    		SET @Cols = COALESCE(@Cols + ',','') + @ColumnName
    	END
    	ELSE
    	BEGIN
    		SET @Rtn = 0
    	
    		SET @sql = N'SET @Rtn = (SELECT COUNT(*) FROM ' + QUOTENAME(@Table) + ' WHERE ' + QUOTENAME(@ColumnName) + ' IS NULL)'
    		EXEC sp_executesql @sql, N'@Rtn INT OUTPUT', @Rtn OUTPUT
    		IF @Rtn = 0
    		BEGIN
    			SET @Cols = COALESCE(@Cols + ',','') + @ColumnName
    		END
    	END
    
    	FETCH NEXT FROM curGetCols INTO @Table, @ColumnName, @Is_Nullable
    END
    
    CLOSE curGetCols
    DEALLOCATE curGetCols
    
    SET @sql = N'SELECT ' + @Cols + ' FROM ' + QUOTENAME(@Table) + '--WHERE SOMETHING'
    PRINT @sql
    --exec sp_executesql @sql

    http://jahaines.blogspot.com/
    Wednesday, June 17, 2009 6:43 PM
  • What about this? The db engine will filter out all null values from the returned xml document by default... And yet, I have to agree: this is a stupid request, so you'll get stupid code. Get your contractor to deliver proper specifications for the thing they need done.

    select *
    from Person.Address
    for xml path('address'), type
    Wednesday, June 17, 2009 7:22 PM
  • Here i have added Is_nullable clause as Adam suggested 

    
    
    declare @tableid int
    declare @tablename sysname
    
    set @tablename = N'Purchasing.Vendor'
    
    declare @columns table
    (
    columnname sysname,
    hasdata int default(0)
    )
    
    select @tableid = id
    from sys.sysobjects
    where name = PARSENAME ( @tablename,1)
    
    INSERT INTO @columns (columnname)
    select name from sys.syscolumns
    where id = @tableid and isnullable = 1
    order by colorder
    
    declare @columnname varchar(255)
    declare @rowcount int
    declare @sql nvarchar(1000)
    declare @ParmDefinition nvarchar(500);
    
    set @ParmDefinition = N'@rowcount int OUTPUT';
    
    select @columnname = min(columnname)
    from @columns
    
    while @columnname is not null
    begin
    	 set @sql = 'select @rowcount = count(*) from ' + @tablename + ' where isnull(convert(varchar(4000), '+@columnname+'), '''') != '''''
    	 print @sql
    
    	 EXECUTE sp_executesql @sql, @ParmDefinition, @rowcount=@rowcount OUTPUT;
    
    	 update @columns
    	 set hasdata = @rowcount
    	 where columnname = @columnname
    
    	 select @columnname = min(columnname)
    	 from @columns
    	 where columnname > @columnname
    end
    
    declare @column_name  nvarchar(max)
    
    select @column_name = substring ((select ', ' + name
    from sys.syscolumns
    where id = @tableid 
    and name not in ( select columnname from @columns where hasdata = 0 )
    for xml path('')),3,8000) 
    
    set @column_name = N'select ' + @column_name + ' from ' + @tablename 
    
    exec sp_executesql @column_name
    

    Thanks,
    Nimit
    Wednesday, June 17, 2009 7:23 PM
  • >You will only need to check the columns that have Is_Nullable set to true.

    Good catch!
    Thursday, June 18, 2009 11:16 AM