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
.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; }
[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
