Script to estimate row sizes for any table

I am trying to estimate the space requirements for a central database server that will be collecting data from about 200 identical field databases. I have the average daily row count for each table and now need to estimate the row size, including indexes, for each table.

Is there such an animal in existence or do I need to roll my own? If I do need to roll my own, can you suggest a good approach?

TIA

Using Aaron's link for even finer adaption of my process, I would recommend a function and a view/query to report out size per row per table, including indexes.

create function dbo.getColumnSize (@typeName SYSNAME, @max_length INT, @precision INT)
RETURNS INT
AS
BEGIN
    RETURN (SELECT CASE @typeName
        WHEN 'tinyint'          THEN 1
        WHEN 'smallint'         THEN 2
        WHEN 'int'              THEN 4
        WHEN 'bigint'           THEN 8
        WHEN 'numeric'          THEN ((@precision - 1)/2) + 1
        WHEN 'decimal'          THEN ((@precision - 1)/2) + 1
        WHEN 'real'             THEN 4
        WHEN 'float'            THEN CASE WHEN @precision <=24 THEN 4 ELSE 8 END
        WHEN 'money'            THEN 8
        WHEN 'smallmoney'       THEN 4
        WHEN 'time'             THEN 5
        WHEN 'timestamp'        THEN 5
        WHEN 'date'             THEN 3
        WHEN 'smalldatetime'    THEN 4
        WHEN 'datetime'         THEN 8
        WHEN 'datetime2'        THEN 8
        WHEN 'datetimeoffset'   THEN 10
        WHEN 'char'             THEN @max_length
        WHEN 'varchar'          THEN @max_length + 2
        WHEN 'nchar'            THEN @max_length
        WHEN 'nvarchar'         THEN @max_length + 2
        WHEN 'binary'           THEN @max_length
        WHEN 'varbinary'        THEN @max_length + 2
        WHEN 'bit'              THEN 0.125
    END)
END

select SchemaName, ObjectName, SUM(CEILING(Bytes))+4+2+2+2+(ceiling(count(distinct columnname)*1.0/8.0)) As RowSize--, FLOOR((POWER(2,30)+(POWER(2,30)-1))*1.0/SUM(CEILING(Bytes))/30/2/60/24)
from (
    select s.name as SchemaName, o.name AS ObjectName, c.name as ColumnName, t.name as TypeName
        , dbo.getColumnSize(t.name,c.max_length, c.precision) AS Bytes
    from sys.objects o
        inner join sys.schemas s on s.schema_id=o.schema_id
        inner join sys.columns c on o.object_id=c.object_id
        inner join sys.types t on c.system_type_id=t.system_type_id
            and t.user_type_id=c.user_type_id
    where o.type='U'
        AND RIGHT(t.name,4) != 'text'
    UNION ALL
    select s.name as SchemaName, o.name AS ObjectName, c.name as ColumnName, t.name as TypeName
        , dbo.getColumnSize(t.name,c.max_length, c.precision) AS Bytes
    from sys.objects o
        inner join sys.schemas s on s.schema_id=o.schema_id
        inner join sys.indexes i on i.object_id = o.object_id
        inner join sys.index_columns ic on ic.object_id = o.object_id
            and i.index_id = ic.index_id
        inner join sys.columns c on o.object_id=c.object_id
            and ic.column_id = c.column_id
        inner join sys.types t on c.system_type_id=t.system_type_id
            and t.user_type_id=c.user_type_id
    where o.type='U'
        AND RIGHT(t.name,4) != 'text'
    ) Z
group by SchemaName, ObjectName

StackExchange Database Administrators Q#66471, answer score: 10