Find the record count in each column (exclude blank and null values) of table in entire database

Question

I would like to find the record count of each columns of the all the tables in the entire database. The count must exclude null and blank rows of that column. I tried the following code, but it displays the row count for that table.

select
    db_name() DatabaseName, 
    s.name + '.' + o.name TableName, c.name ColumnName, sum(p.rows) RecordCount
    from sys.indexes i inner join sys.partitions p
        on i.[object_id] = p.[object_id] and i.index_id = p.index_id
    inner join sys.objects o
        on o.[object_id] = i.[object_id]
    inner join sys.columns c
        on o.[object_id] = c.[object_id]
    inner join sys.schemas s
        on o.[schema_id] = s.[schema_id] 
    where i.index_id < 2
        --and s.name in ('dbo', 'your_other_schema')
        and o.type = 'U'
    group by s.name, o.name , c.name
    order by s.name, o.name;

The code output.

https://i.sstatic.net/kNNRZ.png

The column system_id has 5767 row count where as objectstate has only 2589 row count (excluding blank and null values)

So I expect the OBJECTSTATE column count to be 2589, SYSTEM_ID count to 5767

The output like

Table                Column          Count
Table1               Column1         50  (excluding null and blank row)
Table1               Column2         45  (excluding null and blank row)
Table2               Column1         100 (excluding null and blank row)
Table2               column2         69  (excluding null and blank row)

Answer 1

As documented here, sys.partitions.rows "Indicates the approximate number of rows in this partition."

Joining sys.partitions to sys.objects and sys.columns doesn't help because you still have the rows only on partition level.

That said, you're querying the metadata of the database. Doing this, you cannot obtain the counts you need.

What you have to do is execute a query for each table to get the counts for the table's columns. A query to do this for one particular table (e.g. Table1) might be:

SELECT 
   -- col1 would be a string (varchar, char, etc.) column
   count(nullif(col1, '')) as col1_count,
   -- col2 would not be a string column
   count(col2) as col2_count
FROM Table1

The above query leaves you with a result set containing one colum for each original column and one single row containing the counts. You could change the query to use PIVOT to swap columns and rows which would give you your desired structure.

To get what you want for all tables without the manual work, you could use your original query (just the join of sys.objects and sys.columns though) and loop through that query's results using a CURSOR. In this loop you'd generate the queries for all the tables as strings or one big query string with some UNION ALL. These strings/this string would then have to be executed using EXEC()

Example:

DECLARE @MyQuery NVARCHAR(MAX)
SET @MyQuery = 'SELECT ... FROM Table1 UNION ALL SELECT ... FORM Table2 ...'

EXEC(@MyQuery)