How to find count of null value from multiple column in SQL Server

Question

I am trying to find total number of null value from multiple columns.

SQL query:

SELECT 
    COUNT( * )-COUNT(externalData) As A, 
    COUNT( * )-COUNT(CID) As B,
    COUNT( * )-COUNT(PID) As C
FROM 
    dbo.External_Member_Data;

Which returns the correct output.

My issue is: I have more than 20 tables, each table has more than 20 columns. I want count of null value for all column. Do I need to write individual query for all the table.

Is there any best approach?

You could generate the SQL script. You can write the script generator in SQL. That's fun. `SELECT 'SELECT ...' ...` — Stefan Steinegger, Jan 04 '16 at 10:10
Duplicate question dude ...Check this http://stackoverflow.com/questions/16528682/count-null-values-from-multiple-columns-with-sql — vikram mistry, Jan 04 '16 at 10:11
Which RDBMS are you using? Please add tags for the appropriate RDBMS. — TT., Jan 04 '16 at 10:11
@vikrammistry It's not a duplicate. The OP has the technical solution. He wants a more generic solution where he doesn't need to write explicit code for each table and column. — Stefan Steinegger, Jan 04 '16 at 10:12
@vikrammistry This is not duplicate question. That question is only for few columns, which is working. My question is something different — Ubiquitous Developers, Jan 04 '16 at 10:13
@UbiquitousDevelopers [This SO question](http://stackoverflow.com/questions/13017824/count-the-number-of-nulls-in-each-column) is for 600+ columns and the answer has been accepted. Check it out — Akshay Damle, Jan 04 '16 at 10:19
@AkshayDamle: yeah, that's what I was talking about in my first comment.. — Stefan Steinegger, Jan 04 '16 at 10:22

score 2 · Answer 1 · edited May 30 '17 at 18:18

2

If statistics running on the database, table and columns involved, for each table you could try:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CURRENT_ROWS, NUMBER_DISTINCT_VALUES, NUMBER_NULLS
FROM SchemaDB.SYSCOLUMNSTAT
WHERE TABLE_NAME = 'TableName'

OR multi-tables:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CURRENT_ROWS, NUMBER_DISTINCT_VALUES, NUMBER_NULLS
FROM SchemaDB.SYSCOLUMNSTAT
WHERE TABLE_NAME IN  ('TableName1','TableName2',...,'TableName_n')

or possibly a where clause on TABLE_SCHEMA. Documentation says NUMBER_NULLS is an estimate though.

edited May 30 '17 at 18:18

Paul Roub

36,322
27
84
93

answered May 30 '17 at 18:06

Charlie

21
3

Thanks for reply. But I can check your query now, as I don't have access of database anymore – Ubiquitous Developers May 31 '17 at 05:55

score 1 · Answer 2 · answered Jan 04 '16 at 10:47

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
    SELECT '
    ' +
    STUFF((
        SELECT ', [' + c.name + '] = ' + CASE WHEN c.is_nullable = 0 THEN '0' ELSE 'COUNT(*) - COUNT([' + c.name + '])' END
        FROM sys.columns c
        WHERE c.[object_id] = o.[object_id]
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, 'SELECT ''' + SCHEMA_NAME(o.[schema_id]) + '.' + o.name + ''', COUNT(*), ') + '
    FROM [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']'
    FROM sys.objects o
    WHERE o.[type] = 'U'
        AND o.is_ms_shipped = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

PRINT @SQL
EXEC sys.sp_executesql @SQL

output -

SELECT 'Production.ProductCostHistory', COUNT(*), [ProductID] = 0, [StartDate] = 0, [EndDate] = COUNT(*) - COUNT([EndDate]), [StandardCost] = 0, [ModifiedDate] = 0
FROM [Production].[ProductCostHistory]

SELECT 'Production.ProductDescription', COUNT(*), [ProductDescriptionID] = 0, [Description] = 0, [rowguid] = 0, [ModifiedDate] = 0
FROM [Production].[ProductDescription]

SELECT 'Sales.ShoppingCartItem', COUNT(*), [ShoppingCartItemID] = 0, [ShoppingCartID] = 0, [Quantity] = 0, [ProductID] = 0, [DateCreated] = 0, [ModifiedDate] = 0
FROM [Sales].[ShoppingCartItem]

SELECT 'Production.ProductDocument', COUNT(*), [ProductID] = 0, [DocumentNode] = 0, [ModifiedDate] = 0
FROM [Production].[ProductDocument]

SELECT 'dbo.DatabaseLog', COUNT(*), [DatabaseLogID] = 0, [PostTime] = 0, [DatabaseUser] = 0, [Event] = 0, [Schema] = COUNT(*) - COUNT([Schema]), [Object] = COUNT(*) - COUNT([Object]), [TSQL] = 0, [XmlEvent] = 0
FROM [dbo].[DatabaseLog]

results -

                          BusinessEntityID PersonType  NameStyle   Title       FirstName   MiddleName  LastName    Suffix      EmailPromotion AdditionalContactInfo Demographics rowguid     ModifiedDate
------------- ----------- ---------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -------------- --------------------- ------------ ----------- ------------
Person.Person 19972       0                0           0           18963       0           8499        0           19919       0              19962                 0            0           0

How to find count of null value from multiple column in SQL Server

2 Answers2