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?
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