_View Table Sizes

--DROP TABLE #table_sizes
GO
CREATE TABLE #table_sizes(
    [schema_name] [nvarchar](50),
    [table_name] [nvarchar](255),
    [size_mb] [decimal](18, 2),
    [row_count] [int])


DECLARE tables_cursor CURSOR
    FOR
        select TOP 100 percent
            t.object_id,t.name as schema_name,t.TableName,t.TableSize
        from
            (select
                  sys.objects.object_id,
                  sys.schemas.name,
                  sys.objects.name as TableName,
                  sum(reserved_page_count) * 8.0 / 1024 as TableSize,
                  sys.objects.type
            from sys.objects inner join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
                  ,sys.dm_db_partition_stats
            where
                  sys.dm_db_partition_stats.object_id = sys.objects.object_id
            group by sys.objects.object_id,sys.schemas.name,sys.objects.name,sys.objects.type) t

        WHERE t.type = 'u'
        ORDER BY t.TableSize desc

    OPEN tables_cursor;

    DECLARE @sys_id int;
    DECLARE @tablename sysname;
    DECLARE @schema_id varchar(20);
    DECLARE @tablesize decimal(18, 2);
    DECLARE @tablesize_desc varchar(20);

    FETCH NEXT FROM tables_cursor INTO @sys_id,@schema_id,@tablename,@tablesize;
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN;

        DECLARE @Command nvarchar(1000);
        DECLARE @ParmDefinition nvarchar(500);
        DECLARE @Result int;

        SET @Command = N'SELECT @RCOUNTOUT = COUNT(*) FROM [' + @schema_id + '].[' + @tablename + ']';
        SET @ParmDefinition = N'@RCOUNTOUT int OUTPUT';

        EXECUTE sp_executesql
            @Command
            ,@ParmDefinition
            ,@RCOUNTOUT = @Result OUTPUT;

        INSERT INTO #table_sizes
            ([schema_name]
           ,[table_name]
           ,[size_mb]
           ,[row_count])
        VALUES
            (@schema_id,
            @tablename,
            @tablesize,
            @Result)

       FETCH NEXT FROM tables_cursor INTO @sys_id,@schema_id,@tablename,@tablesize;

    END;

    CLOSE tables_cursor;
    DEALLOCATE tables_cursor

SELECT
    [schema_name]
    ,[table_name]
    ,[size_mb]
    ,REPLACE(CONVERT(varchar(20), (CAST([row_count] AS money)), 1), '.00', '') as [row_count]
    ,[avg_row_size_kb]
FROM
(
    SELECT
    *
    --,(size_mb * 1024) as size_kb
    ,CAST((((size_mb * 1024)) / (row_count + .000001)) as decimal(18,2)) as [avg_row_size_kb]
    FROM #table_sizes
) t
WHERE  t.table_name <> 'sysdiagrams'
ORDER BY
t.row_count desc
,t.size_mb desc
,t.[avg_row_size_kb] desc


SELECT
    SUM(size_mb) as dbsize_mb
    ,COUNT(*) as tablecount
    ,REPLACE(CONVERT(varchar(20), (CAST(SUM(row_count) AS money)), 1), '.00', '') as totalrows
    ,CAST((SUM((size_mb * 1024)) / SUM(row_count)) as decimal(18, 4)) as [avg_row_size_kb]
FROM #table_sizes


--SELECT * FROM #table_sizes
--ORDER BY #table_sizes.table_name asc

DROP TABLE #table_sizes