Use UPDATE STATISTICS to fake table & index sizes

update-statistics.sql

DECLARE @object_id int=OBJECT_ID('dbo.tablename'),
        @rowcount bigint=10000000;




SELECT N'UPDATE STATISTICS '+
            --- Name of the table
            QUOTENAME(OBJECT_SCHEMA_NAME(@object_id))+N'.'+QUOTENAME(OBJECT_NAME(@object_id))+
            --- Name of the index (unless it's a heap)
            ISNULL(N' ('+QUOTENAME(i.[name])+N')', N'')+N' '+
       --- New row count:
       N'WITH ROWCOUNT='+CAST(@rowcount AS nvarchar(20))+', '+
       --- New page count:
       N'PAGECOUNT='+CAST(CAST(@rowcount*(CASE WHEN SUM(p.[rows])>1000
            THEN
                --- Inherit fill factor, compression, etc from existing data if we have at least 1000 existing rows:
                1.*SUM(ps.reserved_page_count)/SUM(p.[rows])
            ELSE
                --- Calculate new values if there are no current rows to model on:
                c.index_width*AVG(x.compression_coefficient)/x.fill_factor*(CASE WHEN i.filter_definition IS NOT NULL THEN 0.5 ELSE 1.0 END)/8060
            END) AS int) AS nvarchar(20))+N';' AS [T-SQL],

       --- Here's what we have today:
       N'-- '+CAST(SUM(p.[rows]) AS nvarchar(20))+N' rows, '+
           CAST(SUM(ps.used_page_count) AS nvarchar(20))+N' pages used, '+
           CAST(SUM(ps.reserved_page_count) AS nvarchar(20))+N' reserved'+
           (CASE WHEN MAX(p.partition_number)>1 THEN N', '+CAST(MAX(p.partition_number) AS nvarchar(20))+N' partitions' ELSE N'' END) AS [Current],
       
       --- Here's what we have to work with:
       CAST(CAST(c.index_width AS int) AS nvarchar(10))+' bytes per row'+
           ISNULL(N', '+CAST(CAST(100-AVG(100*x.compression_coefficient) AS int) AS nvarchar(10))+N'% compression', N'')+
           N', '+CAST(CAST(100*x.fill_factor AS int) AS nvarchar(10))+'% fill factor'+
           (CASE WHEN i.filter_definition IS NOT NULL THEN N', index is filtered' ELSE N'' END) AS Calculation

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 i ON p.[object_id]=i.[object_id] AND p.index_id=i.index_id
CROSS APPLY (
    SELECT (CASE WHEN i.fill_factor=0 THEN 1.0 ELSE 0.01*i.fill_factor END) AS fill_factor,
           (CASE p.[data_compression]
            WHEN 0 THEN 1.0
            WHEN 1 THEN 0.8 -- row compression
            WHEN 2 THEN 0.6 -- page compression
            WHEN 3 THEN 0.3 -- columnstore
            WHEN 4 THEN 0.2 -- columnstore archive
            END) AS compression_coefficient
    ) AS x
CROSS APPLY (
    --- How wide (in bytes) will the index be? Assume 50% usage for var* columns:
    SELECT SUM((CASE WHEN t.[name] LIKE 'var%' THEN 0.5 ELSE 1.0 END)*c.max_length) AS index_width
    FROM sys.columns AS c
    INNER JOIN sys.types AS t ON c.system_type_id=t.user_type_id
    WHERE c.[object_id]=@object_id
      AND (i.index_id IN (0, 1) -- Clustered index or heap will include all columns
        OR i.index_id NOT IN (0, 1) AND c.column_id IN (
            --- Index keys and included columns for non-clustered indexes:
            SELECT column_id
            FROM sys.index_columns AS ic
            WHERE ic.[object_id]=@object_id AND ic.index_id=i.index_id))
    ) AS c
WHERE p.[object_id]=@object_id
  AND p.[rows]!=@rowcount
GROUP BY i.index_id, i.[name], i.filter_definition, c.index_width, x.fill_factor
ORDER BY i.index_id;

This little script will generate the T-SQL statements you need to fake a table's size, taking into account fill factor, compression, existing row density (if you have at least 1000 existing rows).

@dhmacher there's a typo. Line 61 has 1 too many ")". Removed that and it worked. Thanks, this is useful for an issue we're having with SQL Server 2017.

@mbourgon, thanks! I've updated the gist accordingly.