DROP FUNCTION dbo.udf_GetAverageRowSize
Go

CREATE FUNCTION dbo.udf_GetAverageRowSize (@TableName SYSNAME)
RETURNS @AverageRowSize TABLE
   (
       DatabaseName            SYSNAME,
       TableName               SYSNAME,
       IndexName               SYSNAME NULL,
       IndexID                 INT NULL,

       IndexTypeDesc           VARCHAR(50) NULL,
       AllocUnitTypeDesc       VARCHAR(50) NULL,
       MinRecordSizeInBytes    INT NULL,
       MaxRecordSizeInBytes    INT NULL,
       AvgRecordSizeInBytes    DECIMAL(18,2) NULL
   )
AS
/***************************************************************
Purpose:   To retrieve row size statistics from the DMV
          SYS.DM_DB_INDEX_PHYSICAL_STATS for one or all
          tables in a DB
          
          From SQl BOL
             "For an index, one row is returned for each level of the B-tree in each partition.
              For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition.
              For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition.
              If row-overflow data exists in the table, one row is returned for the
              ROW_OVERFLOW_DATA allocation unit in each partition."
          
Author:       Unknown/ChillyDBA
History:   24 Dec 2010 - Initial Issue
          26 Jun 2012 - ChillyDBA - Converted to a table valued function
            with optional table name parameter

****************************************************************/

BEGIN
   INSERT @AverageRowSize  
       (
           DatabaseName,
           TableName,
           IndexName,
           IndexID,
           IndexTypeDesc,
           AllocUnitTypeDesc,
           MinRecordSizeInBytes,
           MaxRecordSizeInBytes,
           AvgRecordSizeInBytes
       )
   SELECT
       CAST(DB_NAME() AS VARCHAR(20))                  AS 'DatabaseName',
       CAST(OBJECT_NAME(ips.object_id) AS VARCHAR(20)) AS 'TableName',
       si.name                                         AS 'IndexName',
       ips.index_id                                    AS 'IndexID',
       ips.index_type_desc                             AS 'IndexTypeDesc',
       ips.alloc_unit_type_desc                        AS 'AllocUnitTypeDesc',
       ips.min_record_size_in_bytes                    AS 'MinRecordSizeInBytes',
       ips.max_record_size_in_bytes                    AS 'MaxRecordSizeInBytes',
       ips.avg_record_size_in_bytes                    AS 'AvgRecordSizeInBytes'
   FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,'DETAILED') ips
   INNER JOIN sys.indexes si
       ON ips.object_id = si.object_ID
       AND ips.index_id = si.index_id
   WHERE ips.object_id  = ISNULL(OBJECT_ID(@TableName), ips.object_id)
   ORDER BY
       OBJECT_NAME(ips.object_id),
       ips.index_id,
       ips.avg_record_size_in_bytes DESC
      
   RETURN
END

GO

USE Adventureworks
GO

SELECT * FROM dbo.udf_GetAverageRowSize(NULL)

SELECT * FROM dbo.udf_GetAverageRowSize('DimCustomer')