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:
Output:
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!
ssisblog 