TSQL: Finding Maximum, Minimum and Average Data Row Lengths

This is probably a classic example of if you find you are doing something complicated, there’s almost certainly a better way. It’s also an example of if you think something is genuinely useful and can’t understand why it’s not been implemented already, it probably has but you just haven’t found it yet!

I wanted to get a table’s approximate minimum, maximum and average row size, so after a few attempts I came up with this TSQL snippet:

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/white-space: pre;/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

declare @schemaname varchar(200) = 'Person'
declare @tablename  varchar(200) = 'Person'

declare @columnList nvarchar(max)
declare @sql        nvarchar(max) 

set  @columnList = 
stuff( 
    (    SELECT
           ' + ISNULL(DATALENGTH(' + c.name + '),0)'
     FROM 
        sys.tables AS t
        INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID 
   where SCHEMA_NAME(schema_id) = @schemaname and t.name = @tablename
    for xml path('') 
    ) 
, 1, 3, '') 

SET @sql = 'SELECT ''' + @schemaname + '.' + @tablename + ''' as TableName,' + 
           ' MIN(' + @columnList + ') AS MinRowLength, ' + 
           ' MAX(' + @columnList + ') AS MaxRowLength, ' + 
           ' AVG(' + @columnList + ') AS AverageRowLength ' + 
           ' FROM [' + @schemaname + '].[' + @tablename + ']'
EXEC sp_executesql @sql

[Note: It’s not 100% accurate due to some row overheads, but it is close enough for many purposes.]

At which point, the thought “Surely there exists a built-in method to do this?” crossed my mind, and of course there is!

If you have sufficient permissions you can quickly produce a min, max and average row size for each table in a database (along with fragmentation information) using

   DBCC SHOWCONTIG WITH TABLERESULTS