How can I dynamically count all rows that have a null value in all tables and columns of a database schema

advertisements

I need to create an exception report that looks over all the tables in a specific schema, which then counts across columns and rows and lists how many entries per column there were that contained a NULL Value.

How is this done without the use of cursors?

Example:

People_Table has 3 Columns (NAME, SURNAME, CONTACT_NO)
Entity_Table has 5 Columns (ID, NAME, ADDRESS, TEL_NO, FAX_NO)

I need to produce an output that lists each table and column name along with how many records contained a NULL value within each of the Columns.

People_Table : NAME (4), SURNAME (9), CONTACT_NO (120)
Entity_Table : ID (0), NAME (4), ADDRESS (90), TEL_NO (120), FAX_NO (100)

Please note that the output can be in a regular dataset format and doesn't need to look like a concatenation of entries! I only listed it in that manner to describe the data output.

This is for a dynamic database that changes over time as a column that at this stage that doesn't have any NULL entries may have a NULL entry in the future, so I need to track this. So far I've done other stuff towards this solution and this is the last of what I need now. Any advice??


Try this (uses cursors though):

DECLARE @tbl sysname
DECLARE @col sysname
DECLARE @sql nvarchar(max)
DECLARE @cnt INT

CREATE TABLE #result
(
    tbl sysname,
    col sysname,
    nulls int
)

DECLARE crs CURSOR FOR
select t.name, c.name
from sys.columns c
join sys.tables t on c.object_id = t.object_id

OPEN crs

FETCH NEXT FROM crs INTO @tbl, @col

WHILE @@FETCH_STATUS=0
BEGIN

    SET @sql = 'select @cntOUT=count(*)  from '[email protected]+' where '[email protected]+' is null'

    SET @cnt = 0
    exec sp_executesql @sql, N'@cntOUT INT OUTPUT', @[email protected] OUTPUT

    INSERT INTO #result (tbl, col, nulls)
    VALUES (@tbl, @col, @cnt)

    FETCH NEXT FROM crs INTO @tbl, @col
END

CLOSE crs
DEALLOCATE crs

SELECT * FROM #result

DROP TABLE #result