Ignore columns having all NULL values while selecting from Table

Lakhwinder.Ghuman
Posted by Lakhwinder.Ghuman under Sql Server category on | Points: 40 | Views : 1039
Ignore columns having 100% NULL values in SELECT statement

The following script ignores all those columns which have 100% NULL values and selects all other columns while fetching records from a table:
(Note: You just need to pass the name of the table)
DECLARE @TableName VARCHAR(500)
SET @TableName='UserRegistration'

DECLARE @TableId INT
SELECT @TableId=object_id FROM sys.tables WHERE name=@TableName

DECLARE @Qry VARCHAR(MAX)
SET @Qry='Select Count(*) From '+@TableName
DECLARE @TotalRowsTable TABLE (NoOfRows INT)
INSERT INTO @TotalRowsTable
EXEC(@qry)

DECLARE @TotalRows INT
SELECT @TotalRows=NoOfRows FROM @TotalRowsTable

DECLARE @TotalNullCount Int
DECLARE @ColName VARCHAR(500)
DECLARE @FinalQry VARCHAR(MAX)
DECLARE @MyTable TABLE (NAME VARCHAR(500), Id INT)
DECLARE @MyTable2 TABLE (ColName VARCHAR(500),VALUE INT)

INSERT INTO @MyTable
SELECT name,column_id FROM sys.columns WHERE object_id=@TableId

DECLARE @C INT
SET @C=1
SET @FinalQry=''
DECLARE MyCursor CURSOR FOR
SELECT Name FROM @MyTable
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @ColName
WHILE @@FETCH_STATUS = 0
BEGIN

SET @Qry= 'Select '+''''+@ColName+''''+', Count(1) FROM '+@TableName+' Where '+@ColName+' IS NULL'
INSERT INTO @MyTable2
EXEC (@qry)
IF NOT EXISTS(SELECT ColName FROM @MyTable2 WHERE ColName=@ColName AND VALUE=@TotalRows)
BEGIN
IF @FinalQry=''
SET @FinalQry= 'Select '+@ColName
ELSE
SET @FinalQry= @FinalQry+','+@ColName
END
FETCH NEXT FROM MyCursor INTO @ColName
END
SET @FinalQry= @FinalQry+' From '+@TableName
--PRINT @FinalQry
EXEC (@FinalQry)
CLOSE MyCursor
DEALLOCATE MyCursor

Comments or Responses

Login to post response