How To Find Space Used by Tables and Indexes in SQL Server – Part 3

USE Utility;
GO
CREATE PROCEDURE dbo.AssessDistribution_ForMultipleTables
  @DatabaseName   nvarchar(260),
  @TableCount     tinyint       = 5,
  @PrimaryFactor  char(4)       = 'rows' -- or 'size'
AS
BEGIN
  SET NOCOUNT ONIF DB_ID(@DatabaseName) IS NULL
  BEGIN
    RAISERROR(N'Database %s does not exist.', 11, 1, @DatabaseName);
    RETURN  END
  DECLARE @sql            nvarchar(max) = N';WITH x AS (SELECT TOP (@TableCount) object_id, ',
          @SchemaName     sysname,
          @ObjectName     sysname,
          @RowOrPageCount int,
          @c              cursor,
          @Context        nvarchar(1024) = COALESCE(QUOTENAME(@DatabaseName) + N'.', N'')
                                           + N'sys.sp_executesql'DECLARE @t table 
  (
    rn             tinyint PRIMARY KEY, 
    SchemaName     sysname, 
    ObjectName     sysname,
    RowOrPageCount int
  );
  IF @PrimaryFactor = 'rows'
  BEGIN
    SET @sql += N'c = SUM(rows) FROM sys.partitions'  END
  IF @PrimaryFactor = 'size'
  BEGIN
    SET @sql += N'c = COUNT(*) FROM sys.dm_db_database_page_allocations
                                    (DB_ID(), NULL, NULL, NULL, N''LIMITED'')'  END
  SET @sql += N'    WHERE OBJECTPROPERTY(object_id, N''IsMsShipped'') = 0
                      -- AND index_id IN (0,1)
                    GROUP BY object_id
                    ORDER BY c DESC
                 )
                 SELECT rn             = ROW_NUMBER() OVER (ORDER BY x.c DESC),
                        SchemaName     = s.name, 
                        ObjectName     = o.name,
                        RowOrPageCount = x.c
                   FROM x
                   INNER JOIN sys.objects AS o ON x.object_id = o.object_id
                   INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id;'
  INSERT @t EXEC @Context @sql, N'@TableCount int', @TableCount;
  IF @@ROWCOUNT = 0
  BEGIN
    RAISERROR(N'No tables found.', 11, 1    RETURN  END
  SET @c = cursor FORWARD_ONLY STATIC READ_ONLY FOR 
    SELECT SchemaName, ObjectName, RowOrPageCount 
    FROM @t ORDER BY rn;
  OPEN @c;
  FETCH NEXT FROM @c INTO @SchemaName, @ObjectName, @RowOrPageCount;
  WHILE @@FETCH_STATUS <> -1
  BEGIN
    SELECT obj    = QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@ObjectName), 
           factor = @PrimaryFactor, 
           num    = @RowOrPageCount;
    EXEC Utility.dbo.AssessDistribution_ByTable 
      @ObjectName    = @ObjectName, 
      @SchemaName    = @SchemaName, 
      @DatabaseName  = @DatabaseName;
    FETCH NEXT FROM @c INTO @SchemaName, @ObjectName, @RowOrPageCount;
  END
END
GO