Script to get row count for all tables in a SQL Server database

Script to get row count for all tables in a SQL Server database

Script to get row count for all tables in a SQL Server database

This post helps you in writing a Script to get row count for all tables in a SQL Server database. For any database developer or administrator the most common requirement is to identifying the larger tables based on data size, index size and row count. This script can quickly list out all tables in the given database and retrieves the below details:

  • Name: Table Name
  • Rows: Total row count
  • Reserved: Space reserved for the table – MB
  • Data: Total space allocated for data pages – MB
  • Index_size: Total space allocated for Index pages – MB
  • Unused: Unused space – MB

Here is the script to get row count for all tables in a SQL Server database:

When you need to get the list of tables along with the row counts, connect to the database instance open a new query window, paste the above script and execute it. It shouldn’t take much time to execute the script as we are using “sp_spaceused”, for us it took maximum 6 sec on a 3.5 TB database.

Remember there are chances where sp_spaced might give the wrong row counts due to several reasons. There are mainly 3 reasons that cause sp_spaceused gives us the wrong counts:

  • When there are huge DDL changes
  • Statistics are not updated
  • Index maintenance is not being taken care

Here you will find more details and resolution.

Posted in SQL Development, SQL Scripts, SQL Server DBA | Tagged , , , | 5 Comments

Leave a Reply

5 Comments on "Script to get row count for all tables in a SQL Server database"

Notify of
avatar
Sort by:   newest | oldest | most voted
jyoti
Guest

Very useful.

Roshani
Guest

I just saw ur blog . It’s very much clear about the topic and useful too.
Thanks for sharing.

trackback

[…] space information” which can be helpful to identify the critical tables in a given database , here you can find more […]

wpDiscuz