use master
go

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_EstimatedTablesRowSize')
    EXEC ('CREATE PROC dbo.sp_EstimatedTablesRowSize AS SELECT ''stub version, to be replaced''')
GO

alter proc sp_EstimatedTablesRowSize
as
/****************************************************************************************************************
    sp_EstimatedTablesRowSize  
    by Antonios Chatzipavlis - SQLschool.gr

    Version History            Remarks
    1.0        2016-10-06        Initial Version
                            - Calculates all tables as HEAP
                            - Does not take into account indexes
                            - Bit data type calculated as 1/8th of byte
                            - Shows and calculates the maximum row size per table (max size from all columns)
                            - The minimum size of row size is 9 bytes because heap record cannot be smaller than forwarding stub size
                            - Calclulations based on BOL page https://msdn.microsoft.com/en-us/library/ms189124.aspx
                            - Return values
                                schema_name        : Name of table schema
                                table_name        : Name of table
                                num_cols        : Number of all table columns
                                num_variable_cols    : Number of variable lenght columns
                                row_size_bytes        : Row size in bytes

    Feedback: mailto:help@sqlschool.gr
    
    License: 
    sp_EstimatedTablesRowSize is free to download and use for personal, educational, and internal 
    corporate purposes, provided that this header is preserved. Redistribution or sale 
    of sp_EstimatedTablesRowSize, in whole or in part, is prohibited without the author's express 
    written consent.
*****************************************************************************************************************/
begin
    with v0 as
    (
           select 
            s.name as schemas_name           
           ,    o.name as table_name
           ,    c.name as column_name
           ,    t.name as data_type
           ,    c.is_nullable as allows_null
           ,    cast ( case  
            when t.name='bit' then 1/8
            when t.name='image' then 2147483647
            when t.name='text' then 2147483647
            when t.name='ntext' then 1073741834
            when t.name='xml' then 2147483647
            when t.name='geometry' then 2147483647
            when t.name='geography' then 2147483647
            when t.name='varchar' and c.max_length=-1 then 2147483647
            when t.name='nvarchar' and c.max_length=-1 then 2147483647
            when t.name='varbinary' and c.max_length=-1 then 2147483647
            else c.max_length 
            end as bigint) as col_lenght_bytes
           ,    c.precision  as col_precision
           ,    t.max_length as type_lenght_bytes
           ,    t.precision  as type_precision
           ,    case 
            when t.name in ('varchar','nvarchar','varbinary'
                   ,'image','text','ntext','sql_variant'
                   ,'hierarchyid','geometry','geography','xml')
                then 1
            else 0
            end as is_var_length
           from        sys.objects        as o 
           inner join   sys.schemas        as s on s.schema_id = o.schema_id
           inner join   sys.columns        as c on c.object_id = o.object_id
           inner join   sys.types        as t on t.user_type_id = c.user_type_id
           where o.type='U'
    ), v1 as
    (
        select  distinct
            schemas_name
        ,       table_name
        ,       count(column_name) over (partition by schemas_name,table_name ) as num_cols
        ,    sum(case when is_var_length = 0 then col_lenght_bytes else 0 end) over (partition by schemas_name,table_name) as fixed_data_size 
        ,    count(case when is_var_length = 1 then column_name else null end) over (partition by schemas_name,table_name) as num_variable_cols 
        ,       sum(case when is_var_length = 1 then col_lenght_bytes else 0 end) over (partition by schemas_name,table_name)  as max_var_size 
        from v0 
    ), v2 as
    (
        select    schemas_name
        ,    table_name
        ,    num_cols
        ,    num_variable_cols
        ,    fixed_data_size 
            + case when num_variable_cols > 0 then (2+(num_variable_cols*2) + max_var_size) else 0 end  -- variable data size
            + (2 + ( ( v1.num_cols + 7 ) / 8 )) -- null bitmap
            + 4 -- header overhead
            as row_size
        from v1
    ), v3 as
    (
        select    v2.schemas_name as [schema_name]
        ,    v2.table_name
        ,    v2.num_cols
        ,    v2.num_variable_cols
        ,    case
            when v2.row_size < 9 then 9 -- heap record cannot be smaller than forwarding stub size
            else v2.row_size
            end as row_size_bytes
        from v2 
    
    )
    select * from v3
    order by 1,2;
end
go

-- make it system object
EXEC sys.sp_MS_marksystemobject sp_EstimatedTablesRowSize ;
GO