Show the widest tables (or indexed views) by average bytes/row

Widest tables by avg row bytes.sql

/*
    Find the widest tables by bytes/row
*/

SELECT OBJECT_SCHEMA_NAME(ps.[object_id])+N'.'+OBJECT_NAME(ps.[object_id]) AS [Object],
       ix.[name] AS [Index],
       ps.partition_number AS [Partition],
       p.data_compression_desc AS [Compression],
       REPLACE(CONVERT(varchar(20), CAST(SUM(ps.row_count) AS money), 1), '.00', '') AS [Row count],
       REPLACE(CONVERT(varchar(20), FLOOR(CAST(SUM(8192.*ps.in_row_used_page_count)      /NULLIF(SUM(ps.row_count), 0) AS money)), 1), '.00', '') AS [In-row, bytes/row],
       REPLACE(CONVERT(varchar(20), FLOOR(CAST(SUM(8192.*ps.row_overflow_used_page_count)/NULLIF(SUM(ps.row_count), 0) AS money)), 1), '.00', '') AS [Row-overflow, bytes/row],
       REPLACE(CONVERT(varchar(20), FLOOR(CAST(SUM(8192.*ps.lob_used_page_count)         /NULLIF(SUM(ps.row_count), 0) AS money)), 1), '.00', '') AS [LOB, bytes/row],
       REPLACE(CONVERT(varchar(20), FLOOR(CAST(SUM(8192.*ps.used_page_count)             /NULLIF(SUM(ps.row_count), 0) AS money)), 1), '.00', '') AS [Used, bytes/row],
       REPLACE(CONVERT(varchar(20), CAST(SUM(ps.used_page_count) AS money), 1), '.00', '') AS [Pages used]
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.partitions AS p ON ps.[partition_id]=p.[partition_id]
LEFT JOIN sys.indexes AS ix ON ps.[object_id]=ix.[object_id] AND ps.index_id=ix.index_id

WHERE ps.index_id IN (0, 1) -- Only heap/clustered
  AND OBJECT_SCHEMA_NAME(ps.[object_id])!='sys'

GROUP BY ps.[object_id], ps.index_id, ix.[name], ps.partition_number, p.data_compression_desc

ORDER BY SUM(8192.*ps.used_page_count)/NULLIF(SUM(ps.row_count), 0) DESC; -- Sort order