title: How to calculate data length in each column (in a single table) in SQL Server?
body: <p>This code provide a possibility to sum a datalength in a single column:</p>
  
  <pre><code>    select sum(datalength(column_1))
      from my_table
  </code></pre>
  
  <p>This part provide me an information about columns in my_table</p>
  
  <pre><code>SELECT COLUMN_NAME,TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION, DATA_TYPE 
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'my_table' AND TABLE_SCHEMA='my_schema'
  </code></pre>
  
  <p>and now i would like to add a column to second select result with sum of data length for each column, so i nedd a column like this:</p>
  
  <pre><code>sum(datalength(col_1))
  sum(datalength(col_2))
      .  
      .          
      .
  sum(datalength(col_n))
  </code></pre>
  
  <p>Could you help me to prepare a proper select to achieve explained result?</p>
  
  <p>EDIT: I need to count how many megabytes have elements in each column (in all rows):</p>
  
  <p>1) if its int type column it is easy: 
      (size of int * number of rows)</p>
  
  <p>2) when column is a varchar i would like to add size of each varchar in this column</p>