10

I am trying to write a script that will show the number of non-null values in each column as well as the total number of rows in the table.

I have found a couple ways to do this:

SELECT sum(case my_column when null then 1 else 0) "Null Values",
   sum(case my_column when null then 0 else 1) "Non-Null Values"
FROM my_table;

and

SELECT count(*) FROM my_table WHERE my_column IS NULL 
UNION ALL
SELECT count(*) FROM my_table WHERE my_column IS NOT NULL

But these require me to type in each column name manually. Is there a way to perform this action for each column without listing them?

Improve this question
5
  • 1
    Why do you want to do this? You can create a script that will look up the names of all columns from the schema views and construct the queries but it's ugly. What problem are you trying to solve? Unless you are writing some kind of admin script, there are probably better ways to solve the original problem Commented Jun 25, 2014 at 14:23
  • I want to be able to see how many of the values in each column are already populated. Commented Jun 25, 2014 at 14:26
  • your first way is the right approach. explicitly name the columns, most of the time that's what we have to do when writing SELECTS anyway. your other option i guess is some funky dynamic sql and that will be ugly Commented Jun 25, 2014 at 14:28
  • @TaiwanTimmy already populated is a sign that you are probably storing two or more entities in a single row. Perhaps you should break your table in two or more tables (eg a master-detail schema). It will be far easier and faster to join two tables than create a dynamic sql statement Commented Jun 25, 2014 at 14:37
  • here is a little example for retrieving column names from a certain table in a certain database Commented Jun 25, 2014 at 14:42

4 Answers 4

Reset to default
10

You should use execute:

DECLARE @t nvarchar(max)
SET @t = N'SELECT '

SELECT @t = @t + 'sum(case when ' + c.name + ' is null then 1 else 0 end) "Null Values for ' + c.name + '",
                sum(case when ' + c.name + ' is null then 0 else 1 end) "Non-Null Values for ' + c.name + '",'
FROM sys.columns c 
WHERE c.object_id = object_id('my_table');

SET @t = SUBSTRING(@t, 1, LEN(@t) - 1) + ' FROM my_table;'

EXEC sp_executesql @t
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Scratch that, it isn't summing the number NULLs correctly. Each column results in 0 NULLs and all Non NULLs. Ideas?
The problem is with the case statements. Change both of them to this case when ' + c.name + ' is null instead of case ' + c.name + ' when null. This will return the correct counts.
Thanks @JChao. I missed that at the last moment
Hey! Is there a command to select the null values from each column at once?
@Alireza Thank you for replying. My query is solved. I have used the count and group by functions to check the null values.
|
3

As Paolo said, but here is an example:

DECLARE @TableName VARCHAR(512) = 'invoiceTbl';
DECLARE @SQL VARCHAR(1024);
WITH SQLText AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY c.Name) AS RowNum,
        'SELECT ''' + c.name + ''', SUM(CASE WHEN ' + c.Name + ' IS NULL THEN 1 ELSE 0 END) AS NullValues FROM ' + @TableName AS SQLRow
    FROM 
        sys.tables t 
        INNER JOIN sys.columns c ON c.object_id = t.object_id
    WHERE 
        t.name = @TableName),
Recur AS (
    SELECT
        RowNum,
        CONVERT(VARCHAR(MAX), SQLRow) AS SQLRow
    FROM
        SQLText
    WHERE
        RowNum = 1
    UNION ALL
    SELECT
        t.RowNum,
        CONVERT(VARCHAR(MAX), r.SQLRow + ' UNION ALL ' + t.SQLRow)
    FROM
        SQLText t
        INNER JOIN Recur r ON t.RowNum = r.RowNum + 1
    )
SELECT @SQL = SQLRow FROM Recur WHERE RowNum = (SELECT MAX(RowNum) FROM Recur);
EXEC(@SQL);
Improve this answer

8 Comments

Thanks, but this just results with Command(s) completed successfully How do I go about displaying the table?
You would need to change the name of the table (on the very first line) to the name of the table in your database...
It given the same response after changing it to the correct table name
Is your table in a schema other than "dbo"? Also, note that if you run this from a different database you will need to prefix the sys.tables and sys.columns with the database name, e.g. [MyDatabase].sys.tables. I ran it and it worked fine for me... the results just appear in the Results tab of SSMS.
I tried it on a different table that I built for testing and it worked fine. Thanks again
|
1

may be this works 

select count(case when Column1 is null then 1 end) as Column1NullCount,
    count(case when Column2 is null then 1 end) as Column2NullCount,
    count(case when Column3 is null then 1 end) as Column3NullCount
    ...
from My_Table
Improve this answer

1 Comment

But these require me to type in each column name manually. Is there a way to perform this action for each column without listing them? Isn't that exactly what you're doing here?
1

you can go with dynamic sql and sys tables.
depending on the version of sql you are using the syntax will change slightly but these are the steps:
- list the columns reading sys.columns and save the list in a temp table or table variable
- make a loop through that temp table and build the sql using the same logic you would apply manually
- execute the dynamic sql built on previous step with sp_executesql

Improve this answer

1 Comment

how would you structure the loop?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.