Sql-server – SQL Server select columns which contain data

sql servert-sql

I'm working for a client to import data out of a generic off the shelf application database. If you can imagine a database that is designed by software developers that have no concept of database design, that is what i'm dealing with. I'm selecting from tables that have 50+ columns, where half the columns contain null data for every row (i.e. not used). My question is, how do i identify the columns that are null for EVERY row (i.e. useless) and exclude them from my queries? Thanks for your help! (i want to avoid going column by column saying isnull etc. as that will take forever!)

Best Answer

You can use this stored procedure to the trick You need to provide the table name you wish to query note that if you'll pass to procedure the @exec parameter = 1 it will execute the select query

  SET ANSI_NULLS ON
   GO
  SET QUOTED_IDENTIFIER ON
  GO
 CREATE PROCEDURE [dbo].[SP_SELECT_NON_NULL_COLUMNS] ( @tablename varchar    (100)=null, @exec int =0)
  AS BEGIN
  SET NOCOUNT ON
        IF @tablename IS NULL
              RAISERROR('CANT EXECUTE THE PROC, TABLE NAME IS MISSING',16 ,1)
                          ELSE
        BEGIN
              IF OBJECT_ID('tempdb..#table') IS NOT NULL DROP TABLE #table
              DECLARE @i VARCHAR (max)=''
              DECLARE @sentence VARCHAR (max)=''
              DECLARE @SELECT VARCHAR (max)
              DECLARE @LocalTableName VARCHAR(50) = '['+@tablename+']'
              CREATE TABLE  #table  (ColumnName VARCHAR (max))
              SELECT @i+=
              ' IF EXISTS ( SELECT TOP 1 '+column_name+' FROM '  +@LocalTableName+' WHERE ' +column_name+
                   ' '+'IS NOT NULL) INSERT INTO #table VALUES ('''+column_name+''');'
                    FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name=@tablename
                    INSERT INTO #table
                    EXEC (@i)
                     SELECT @sentence = @sentence+' '+columnname+' ,' FROM #table                  
             DROP TABLE #table                 
                    IF @exec=0
                          BEGIN
                                SELECT 'SELECT '+ LTRIM (left (@sentence,NULLIF(LEN (@sentence)-1,-1)))+
                                            +' FROM ' +@LocalTableName
                    END
                    ELSE
                          BEGIN 
                                SELECT @SELECT=  'SELECT '+ LTRIM (left (@sentence,NULLIF(LEN (@sentence)-1,-1)))+
                                                  +' FROM '+@LocalTableName
                        EXEC (@SELECT)
                    END
  END
  END
Related Question