Single Sql Query To Find Null Values In All Columns In A Data Base

I would like to identify the number of null values in each column in all tables.I have a data base it consist of around 250 tables.Most of them are in use.The problem is almost all

Solution 1:

This is a mess, but it works:

DECLARE@SQL nvarchar(MAX),
        @CRLFnchar(2) =NCHAR(13) +NCHAR(10);

CREATETABLE #NullCounts (SchemaName sysname,
                          TableName sysname,
                          ColumnName sysname,
                          NULLCount bigint);

DECLARE@Delimiternchar(3) =','+@CRLF;

SET@SQL= STUFF((SELECT@CRLF+@CRLF+
                         N'WITH Counts AS('+@CRLF+
                         N'    SELECT N'+ QUOTENAME(s.[name],'''') +N' AS SchemaName,'+@CRLF+
                         N'           N'+ QUOTENAME(t.[name],'''') +N' AS TableName,'+@CRLF+
                         STRING_AGG(N'           COUNT_BIG(CASE WHEN '+ QUOTENAME(c.[name]) + N' IS NULL THEN 1 END) AS '+ QUOTENAME(c.[name]),@Delimiter) WITHINGROUP(ORDERBY c.column_id) +@CRLF+
                         N'    FROM '+ QUOTENAME(s.[name]) + N'.'+ QUOTENAME(t.[name]) + N' T)'+@CRLF+
                         N'INSERT INTO #NullCounts(SchemaName, TableName, ColumnName, NULLCount)'+@CRLF+
                         N'SELECT SchemaName,'+@CRLF+
                         N'       TableName,'+@CRLF+
                         N'       V.ColumnName,'+@CRLF+
                         N'       V.NULLCount'+@CRLF+
                         N'FROM Counts C'+@CRLF+
                         N'     CROSS APPLY (VALUES'+
                         STUFF(STRING_AGG(N'                        (N'+ QUOTENAME(c.[name], '''') + N', C.'+ QUOTENAME(c.[name]) + N')',@Delimiter) WITHINGROUP (ORDERBY c.column_id),1,24,N'') + N')V(ColumnName,NULLCount);'FROM sys.schemas s
                       JOIN sys.tables t ON s.schema_id = t.schema_id
                       JOIN sys.columns c ON t.object_id = c.object_id
                  GROUPBY s.[name], t.[name]
                  FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,4,N'');
     
--PRINT @SQL; --This is gunna be way longer than 4,000 characters, so you'll want SELECTEXEC sys.sp_executesql @SQL;

GO

SELECT*FROM #NullCounts
ORDERBY SchemaName,
         TableName,
         ColumnName;

GO

DROPTABLE #NullCounts;

Yes, I mix STRING_AGG and FOR XML PATH, yes it's an eyesore, but the printed (selected) SQL produces some very nice statements. See below:

WITH Counts AS(
    SELECT N'dbo' AS SchemaName,
           N'PerformanceTest' AS TableName,
           COUNT_BIG(CASEWHEN TestID IS NULL THEN1END) AS [TestID],
           COUNT_BIG(CASEWHEN TestTarget IS NULL THEN1END) AS [TestTarget],
           COUNT_BIG(CASEWHEN TestName IS NULL THEN1END) AS [TestName],
           COUNT_BIG(CASEWHEN TimeStart IS NULL THEN1END) AS [TimeStart],
           COUNT_BIG(CASEWHEN TimeEnd IS NULL THEN1END) AS [TimeEnd],
           COUNT_BIG(CASEWHEN TimeTaken_ms IS NULL THEN1END) AS [TimeTaken_ms],
           COUNT_BIG(CASEWHEN TotalRows IS NULL THEN1END) AS [TotalRows],
           COUNT_BIG(CASEWHEN RowSets IS NULL THEN1END) AS [RowSets],
           COUNT_BIG(CASEWHEN AvgRowsPerSet IS NULL THEN1END) AS [AvgRowsPerSet]
    FROM [dbo].[PerformanceTest] T)
INSERT INTO #NullCounts(SchemaName, TableName, ColumnName, NULLCount)
SELECT SchemaName,
       TableName,
       V.ColumnName,
       V.NULLCount
FROM Counts C
     CROSS APPLY (VALUES(N'TestID', C.[TestID]),
                        (N'TestTarget', C.[TestTarget]),
                        (N'TestName', C.[TestName]),
                        (N'TimeStart', C.[TimeStart]),
                        (N'TimeEnd', C.[TimeEnd]),
                        (N'TimeTaken_ms', C.[TimeTaken_ms]),
                        (N'TotalRows', C.[TotalRows]),
                        (N'RowSets', C.[RowSets]),
                        (N'AvgRowsPerSet', C.[AvgRowsPerSet]))V(ColumnName,NULLCount);WITH Counts AS(
    SELECT N'dbo' AS SchemaName,
           N'someTable' AS TableName,
           COUNT_BIG(CASEWHEN id IS NULL THEN1END) AS [id],
           COUNT_BIG(CASEWHEN SomeCol IS NULL THEN1END) AS [SomeCol]
    FROM [dbo].[someTable] T)
INSERT INTO #NullCounts(SchemaName, TableName, ColumnName, NULLCount)
SELECT SchemaName,
       TableName,
       V.ColumnName,
       V.NULLCount
FROM Counts C
     CROSS APPLY (VALUES(N'id', C.[id]),
                        (N'SomeCol', C.[SomeCol]))V(ColumnName,NULLCount);

And yes, I really spent the last 45 minutes writing all that...

Honestly, this is not entry level, and if you don't understand it, you shouldn't be using it; but also, you I very much doubt you'll find a different solution that is entry level and that is as performant as this. A CURSOR, for example, although probably easier to understand would be really slow doing this.

Caveat: If you have any deprecated data types in your database (i.e. text) this will fail. If that is the case, you will need to ensure you eliminate them from the query in the WHERE. However, I suggest that you fix your data types (text, for example, has been deprecated for 15 years).

Solution 2:

For tabular results:

declare@sql  nvarchar(max) = 
(
select'union all select (select object_schema_name('+cast(tableobjectid asvarchar(20))+') +''.''+ object_name('+cast(tableobjectid asvarchar(20))+') as "table/@name", count(*) as "table/@rowcount", '+ cols_concat +' from '+ tablename +' for xml path(''''), type)'as'text()'from
(
select 
    t.object_id as tableobjectid,
    quotename(schema_name(t.schema_id)) +'.'+ quotename(t.name) as tablename,
    stuff( (select', col_name('+cast(c.object_id asvarchar(20)) +','+cast(c.column_id asvarchar(20)) +') as "table/col/@name", count(*)-count('+casewhen type_name(c.system_type_id) in ('text', 'ntext', 'image') then' case when '+ quotename(c.name) +' is not null then 1 end'else quotename(c.name) end+') as "table/col", null as "table"'from sys.columns as c where c.object_id = t.object_id for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '') as cols_concat
from sys.tables as t
where t.is_ms_shipped =0
) as tbl
for xml path(''), type).value('.', 'nvarchar(max)')
;

select@sql='select @xml = (select * from (select cast(null as xml) as "*"  '+@sql+') as u for xml path('''') )';
declare@x xml;

set transaction isolation level read uncommitted;
exec sp_executesql @sql, N'@xml xml output', @xml=@x output;
set transaction isolation level read committed;

--shred select 
    t.col.value('@name[1]', 'varchar(200)') as tablename,
    t.col.value('@rowcount[1]', 'int') as tablerowcount,    
    r.col.value('@name[1]', 'varchar(200)') as columnname,  
    r.col.value('.[1]', 'int') as null_values
from@x.nodes('table') as t(col)
cross apply t.col.nodes('col') as r(col);

For multiple resultsets (one per table):

declare@sql  nvarchar(max) = 
    (
    select'select object_schema_name('+cast(tableobjectid asvarchar(20))+') +''.''+ object_name('+cast(tableobjectid asvarchar(20))+') as tablename, '+ cols_concat +' from '+ tablename +';'as'text()'from
    (
    select 
        t.object_id as tableobjectid,
        quotename(schema_name(t.schema_id)) +'.'+ quotename(t.name) as tablename,
        stuff( (select', count(*)-count('+casewhen type_name(c.system_type_id) in ('text', 'ntext', 'image') then' case when '+ quotename(c.name) +' is not null then 1 end'else quotename(c.name) end+') as "NULL_'+ replace(quotename(name), '"', '""')+'"'from sys.columns as c where c.object_id = t.object_id for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '') as cols_concat
    from sys.tables as t
    where t.is_ms_shipped =0
    ) as tbl
    for xml path(''), type).value('.', 'nvarchar(max)');

    set transaction isolation level read uncommitted;
    exec(@sql);    
    set transaction isolation level read committed;