title: SQL Server - Reference a column by name in aggregate function
body: <p>Our application uses a series of tables with many varchar(1000) columns in them. During development of the system, that was considered to be more than enough space for the users to enter text. However, now we're encountering problems with text being cut off, so it seems like some users are entering more text that it can hold. However, I believe that there's rampant misuse of these fields, and I want to see which columns are being used.</p>
  
  <p>From a different question that was answered here on SO, I've come up with the following query to extract the names of the varchar columns in a table:</p>
  
  <pre><code>select
     syscolumns.name as [Column],
     syscolumns.length as [MaxLength]
  from 
     sysobjects, syscolumns 
  where sysobjects.id = syscolumns.id
  and   sysobjects.xtype = 'u'
  and   sysobjects.name = 'TableWithTooManyVarcharColumns'
  and syscolumns.xusertype = 167
  </code></pre>
  
  <p>but now I want to use the names of these columns in a new query, and calculate </p>
  
  <pre><code>SELECT 
  [Column] as [Name],
  [MaxLength],
  MAX(LEN([Column])) as [MaxUsedLength],
  AVG(LEN([Column])) as [AvgUsedLength],
  STDEV(LEN([Column])) as [StdDev]
  FROM TableWithTooManyVarcharColumns
  INNER JOIN **{{ reference to table from query above }}**
  </code></pre>
  
  <p>Of course, the first query returns the name of the column, while the second one needs a reference to the column, so I'm not sure how to combine them properly. Any SQL Server gurus able to help?</p>