Get Rowcount and Size for all user tables in a DB

In this script post, we will look at different ways to get the list of user defined tables in a particular database, along with their rowcount and size information.

Method 1: Using count(*)

In this method we will get the rowcount for each table using the sp_msforeachtable in combination with count(*) function. Note using this method we will only get the rowcount for each table.

IF OBJECT_ID('tempdb..#tableRowCount') IS NOT NULL
DROP TABLE #tableRowCount
CREATE TABLE #tableRowCount
(
name       VARCHAR(512),
ROWS       INT
)
insert into #tableRowCount
exec sp_msforeachtable 'select ''?'' ,COUNT(*) FROM ?'
select * from #tableRowCount

Method 2: Using sp_spaceused

Here, we will get the rowcount for each table using the sp_msforeachtable in combination with sp_spaceused stored procedure. Using this method we will get both the rowcount as well as spaceused in KB for all the tables.

IF OBJECT_ID('tempdb..#tablesize_temp') IS NOT NULL
DROP TABLE #tablesize_temp
IF OBJECT_ID('tempdb..#tablesize') IS NOT NULL
DROP TABLE #tablesize
CREATE TABLE #tablesize_temp (
name       VARCHAR(512),
ROWS       INT,
reserved   VARCHAR(51),
data       VARCHAR(51),
index_size VARCHAR(51),
unused     VARCHAR(51))
CREATE TABLE #tablesize (
name       VARCHAR(512),
ROWS       INT,
reserved   BIGINT,
data       BIGINT,
index_size BIGINT,
unused     BIGINT)
insert into #tablesize_temp
exec sp_msforeachtable 'exec sp_spaceused ''?'''
insert into #tablesize
select name, rows,
replace (reserved, 'kb',''),replace (data, 'kb',''),
replace (index_size, 'kb',''), replace (unused, 'kb','')
from #tablesize_temp
select * from #tablesize order by rows desc

Method 3: Using System Tables/Views

Here, we will get the rowcount for each table using the sys.dm_db_partition_stats , sys.tables and sys.schemas.
Note this method will work only for SQL Server 2005 onwards.

;WITH CTE_TABLE_USAGE
AS
(
    SELECT '['+c.name+'].[' + b.name +']' as name
        ,SUM (reserved_page_count ) reservedpages
        ,SUM (used_page_count) usedpages
        ,SUM (
            CASE
                WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                ELSE lob_used_page_count + row_overflow_used_page_count
            END
            ) pages
        ,SUM (
            CASE
                WHEN (index_id < 2) THEN row_count
                ELSE 0
            END
            )[rowCount]
    FROM sys.dm_db_partition_stats a JOIN sys.tables b ON a.object_id= b.object_id
    JOIN sys.schemas c on b.schema_id = c.schema_id
    WHERE b.type = 'U'
    GROUP BY c.name,b.name
)
SELECT name
,[rowCount] as rows
,CAST(reservedpages*8 AS VARCHAR(50))+' KB' as reserved
,CAST(pages *8 AS VARCHAR(50))+' KB' as data
,CAST(CASE WHEN usedpages > pages THEN (usedpages-pages) * 8 ELSE 0 END ASVARCHAR(50)) +' KB' index_size
,CAST(CASE WHEN reservedpages > usedpages THEN (reservedpages-usedpages) ELSE 0 END * 8 AS VARCHAR(50)) +' KB'  as unused
FROM CTE_TABLE_USAGE