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.

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.

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.