For what it's worth, the dynamic management view [sys.dm_db_index_physical_stats] returns statistics for the row, index, and lob pages related to a table object which can be filtered. Each row contains avg, min, and max record size (and a lot of other useful columns) for a page, which can then be aggregated. For a clustered table, the statistics on the clustered index will give you the record counts and stats for the table data itself. For heap (non-clustered) tables, the index_id will be 0 and will return the same. Maybe you can start from here to get what you really want.

http://msdn.microsoft.com/en-us/library/ms188917.aspx

I'm not sure why you would need the row size only for records returned in the resultset. The size of the record in the table or index versus it's size in the network packet or it's size in the application grid, dataset, Excel sheet, etc. will be different, because it's different data structures.

select

cast(db_name(ps.database_id)+'.'+object_name(ps.object_id)+'.'+isnull(i.name,'heap') as varchar(60)) as db_table_index_name,

sum(ps.record_count) as sum_record_count,

-- cast(((sum(ps.page_count) * 8192) / 1000000.00) as numeric(9,2)) as size_mb,

avg(ps.max_record_size_in_bytes) as avg_record_size_in_bytes,

max(ps.max_record_size_in_bytes) as max_record_size_in_bytes

-- cast(avg(avg_fragmentation_in_percent) as numeric(9,1)) as avg_fragmentation_in_percent,

-- cast(avg(ps.avg_page_space_used_in_percent) as numeric(9,1)) as avg_page_space_used_in_percent

from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'detailed') as ps

left join sys.indexes as i on i.object_id = ps.object_id and i.index_id = ps.index_id

where object_name(ps.object_id) in ('employee')

group bydb_name(ps.database_id), object_name(ps.object_id), i.name

order bydb_name(ps.database_id), object_name(ps.object_id), i.name;

DB_Table_Index_Name sum_record_count avg_record_size_in_bytes max_record_size_in_bytes

------------------------------------------------------------ -------------------- ------------------------ ------------------------

AdventureWorks.Employee.AK_Employee_LoginID 293 60 65

AdventureWorks.Employee.AK_Employee_NationalIDNumber 292 28 29

AdventureWorks.Employee.AK_Employee_rowguid 290 21 21

AdventureWorks.Employee.IX_Employee_ManagerID 290 12 12

AdventureWorks.Employee.PK_Employee_EmployeeID 301 115 219