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!)
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
The easy solution is to check every single column that you care about to see if it is NULL.
select Col1, Col2, Col3 from MyBigTable where Col1 IS NOT NULL and Col2 IS NOT NULL and Col3 IS NOT NULL
A trickier and possibly performance-killing solution would be to add a computed bit column to the table that gets set to true if any column is not null. When any of the columns are modified, this column would get re-calculated. Your query would then look like this:
select Col, Col2, Col3 from MyBigTable where IsThereAnyData = 1