title: Query Number of Populated Rows for Each Column in Table (SQL Server)
body: <p>I am trying to get a count of non-null values for each column in my table.  I have researched the following previously asked SO questions, and have not found a satisfactory answer:  </p>
  
  <p><a href="https://stackoverflow.com/questions/1443704/query-to-list-number-of-records-in-each-table-in-a-database">Query to list number of records in each table in a database</a></p>
  
  <p><a href="https://stackoverflow.com/questions/21313615/get-a-count-of-each-value-from-every-column-in-a-table-sql-server">get a count of each value from every column in a table SQL Server</a></p>
  
  <p><a href="https://stackoverflow.com/questions/12625729/sql-server-count-number-of-distinct-values-in-each-column-of-a-table">SQL Server count number of distinct values in each column of a table</a></p>
  
  <p>I have written the following code to try to build out a data dictionary for my table to include the name of the column, the number of populated rows in each column, the data type, the length, and whether it is a primary key:</p>
  
  <pre><code>SELECT 
      c.name 'Column Name',
      p.rows 'Row_Count',
      t.Name 'Data type',
      c.max_length 'Max Length',
      ISNULL(i.is_primary_key, 0) 'Primary Key'
  FROM    
      sys.columns c
  INNER JOIN 
      sys.types t ON c.user_type_id = t.user_type_id
  LEFT OUTER JOIN 
      sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
  LEFT OUTER JOIN 
      sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
  LEFT OUTER JOIN
  sys.partitions p ON p.OBJECT_ID = i.OBJECT_ID and i.index_id = p.index_id
  WHERE
      c.object_id = OBJECT_ID('my_table')
  </code></pre>
  
  <p>However, the Row_Count column returns all Nulls. </p>
  
  <p>My expected outcome looks like this:</p>
  
  <pre><code>Column_Name Row_Count Data_Type Max_Length Primary_Key
  A              10       varchar   50            0
  B              10       varchar   50            0
  C              7        float     50            0
  D              3        float     50            0
  E              10       varchar   50            0
  </code></pre>