Return all table sizes, row counts and column counts

TSQL_TableSize

; -- Previous statement must be properly terminated
WITH tableRowCount
AS
(
    SELECT
        ddps.object_id
        , SUM(ddps.row_count) AS rowCnt
        , SUM(ddps.used_page_count) * 8. / 1024. / 1024. AS usedSize
    FROM sys.dm_db_partition_stats ddps
    WHERE ddps.index_id < 2
    GROUP BY ddps.object_id
)
, columnCount
AS
(
    SELECT
        o.object_id
        , COUNT(c.column_id) AS columnCount
    FROM sys.columns AS c
    JOIN sys.objects AS o ON c.object_id = o.object_id
    WHERE o.is_ms_shipped = 0
    GROUP BY o.object_id
)
, allIndexSize
as
(
    SELECT
        ddps.object_id
        , SUM(ddps.used_page_count) * 8. / 1024. / 1024. AS usedSize
        , SUM(ddps.reserved_page_count) * 8. / 1024. / 1024. AS reservedSize
    FROM sys.dm_db_partition_stats ddps
    GROUP BY ddps.object_id
)
, tableRank AS
(
SELECT
    trc.object_id AS Id
  , OBJECT_NAME(trc.object_id) AS tableName
  , trc.rowCnt
  , cc.columnCount
  , trc.usedSize AS baseUsedSize_GB
  , ais.usedSize AS wholeUsedSize_GB
  , ais.reservedSize AS reservedSize_GB
  --, DENSE_RANK() OVER (ORDER BY trc.usedSize desc) AS orderBase
  --, DENSE_RANK() OVER (ORDER BY ais.usedSize desc) AS orderWhole
  --, DENSE_RANK() OVER (ORDER BY trc.rowCnt desc) AS orderRow
  --, DENSE_RANK() OVER (ORDER BY cc.columnCount desc) AS orderColumn
 
FROM 
    tableRowCount trc
    JOIN allIndexSize ais
        ON trc.object_id = ais.object_id
    JOIN columnCount cc ON trc.object_id = cc.object_id
)
SELECT
    tr.Id
  , OBJECT_SCHEMA_NAME(tr.id) AS schemaName
  , tr.tableName
  , FORMAT(tr.rowCnt, 'N0') AS rowCntFormatted
  , tr.columnCount
  , tr.baseUsedSize_GB
  , tr.wholeUsedSize_GB
  , tr.reservedSize_GB
  , (tr.reservedSize_GB - tr.wholeUsedSize_GB) * 1024. AS unusedSize_MB
  --, tr.orderBase
  --, tr.orderWhole
  --, tr.orderRow
  --, tr.orderColumn
FROM tableRank tr
--WHERE tr.tableName = 'OptionalTableFilter'
ORDER BY
    tr.reservedSize_GB desc
OPTION (RECOMPILE)