Checking for null columns in a table

I had a situation recently where I had to build a table that had over 150 columns, and once the script to load the table was complete and ran, I needed a quick way to check that all columns were getting populated with data. I built this script below to handle it:

CREATE PROC [util].[usp_check_for_null_cols]
@tbl_nm NVARCHAR(255)
,@schema_nm NVARCHAR(255) = ‘dbo’
AS
SET NOCOUNT ON;

DECLARE @cols AS TABLE (col_nm NVARCHAR(255), is_null BIT);

INSERT @cols (col_nm)
SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tbl_nm
AND TABLE_SCHEMA = @schema_nm
;

DECLARE @curr_col NVARCHAR(255)
DECLARE cs CURSOR LOCAL FAST_FORWARD FOR SELECT col_nm FROM @cols
OPEN cs
FETCH NEXT FROM cs INTO @curr_col
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @is_null bit
DECLARE @sql NVARCHAR(MAX) = CONCAT(

IF EXISTS (SELECT 1 FROM [‘,@schema_nm,’].[‘,@tbl_nm,’] WHERE [‘,@curr_col,’] is not null)
BEGIN
SET @is_null = 0
END
ELSE
BEGIN
SET @is_null = 1
END

)
;
DECLARE @params NVARCHAR(MAX) = N’@is_null bit out’
EXEC sp_executesql @sql, @params, @is_null = @is_null OUT

UPDATE @cols SET is_null = @is_null
WHERE col_nm = @curr_col
;

RAISERROR(‘Column [%s] processed’,1,1,@curr_col) WITH NOWAIT;

FETCH NEXT FROM cs INTO @curr_col
END
CLOSE cs
DEALLOCATE cs

SELECT *
FROM @cols
WHERE is_null = 1

Advertisements
Checking for null columns in a table