Can I use system views to get aggregate value for all columns in a table?

Question

I'm in the middle of setting up a table, and I want to look at the count of how many 0 values each of the columns has. I've never used system views before... can I do something like this? (this doesn't work as entered, of course, it's just to convey intent):

SELECT t.COLUMN_NAME, (SELECT COUNT(ID) FROM tblKeyStatistics t2 WHERE 
t2.ColumnName = t.COLUMN_NAME AND t2.ColumnName = 0) AS CountOf0
FROM INFORMATION_SCHEMA.COLUMNS t
WHERE TABLE_NAME = 'tblKeyStatistics'

So output would look like:

EntValue    2
TrailingPE  1
ForwardPE   11

With each of the integers being the count of 0 values in that column in the table.

Thanks in advance...

Answer 1

No, the system views / DMVs do not track values or null/not null status for individual columns, unless there are stored in some undocumented location (which I doubt). To get this information, you'll need to run the queries against the actual objects. You can generate the queries in a more automated way, of course:

DECLARE @table sysname = N'dbo.tblKeyStatistics'; -- please use schema prefix

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += CHAR(13) + CHAR(10) 
  + QUOTENAME(name) + N' =  SUM(CASE WHEN ' 
  + QUOTENAME(name) + N' = 0 THEN 1 ELSE 0 END),'
  FROM sys.columns
  WHERE [object_id] = OBJECT_ID(@table)
  -- make sure you only get numeric types:
  AND system_type_id IN (48, 52, 56, 59, 60, 62, 104, 106, 108, 122, 127);

SELECT @sql = N'SELECT ' + @sql + N' TotalRows = COUNT(*) 
  FROM ' + @table + N';';

PRINT @sql;
--EXEC sys.sp_executesql @sql;

This looks like it will result in a very expensive query, but in reality it just does a clustered index scan (much like SELECT * would do, with a little added math).

Also please stay away from the INFORMATION_SCHEMA views. They are not healthy:

• The case against INFORMATION_SCHEMA views