Tag Archives: removing null records from table

SQL CODE TO GET LIST OF ROWS HAVING NULLS IN ALL COLUMNS

We may encounter a situation wherein the table contains rows that have null values in all the columns. We might need to perform some activity on those rows say clean up the table as those rows will not add value to the business. In this section, I will try to put in the code that takes the column names dynamically and forms a query that you can use further in your code.

Let’s get started:

Input:

Untitled1

Output:

Untitled2

 

SQL Code:

DECLARE @sql nvarchar(max), @whereclause nvarchar(max)

,@tblName nvarchar(100) = ‘tbl’ –Table Name

,@pkColumn nvarchar(100)  = ‘ID’ –Primary Key Column

DECLARE @tbl TABLE

(

ID int IDENTITY(1,1),

ColName nvarchar(50)

)

INSERT INTO @tbl(ColName)

SELECT sc.name FROM sys.columns sc

JOIN sys.tables st

ON sc.object_id  =st.object_id

WHERE st.name = @tblName and sc.name NOT IN (@pkColumn)

SELECT @whereclause = COALESCE(@whereclause + ‘ is NULL AND ‘,” ) + ColName FROM @tbl

SELECT @SQL  = ‘SELECT * FROM     ‘ + @tblName + ‘ WHERE ‘ + @whereclause + ‘ is null’

SELECT @sql

That’s it. We are done.

Happy Coding!