Tips ‘N’ Tricks – SSMS – Quickest way to Get Row Count of All Tables from a SQL Server Database

There are various different ways to get the Row Count from All the Tables in a SQL Server Database as discussed in this article. However, those approaches require querying the tables directly or querying the other systems tables/views which store the statistical information about the data in the database. In this tip we will see an approach which can be used to get the Table Row Counts without writing any explicit queries.

  • Open SQL Server Management Studio (SSMS)
  • Connect to the SQL Server Instance from which you want to get the Row Counts
  • Go to View | Object Explorer Details (or alternatively, you can press F7)
  • In the Object Explorer, navigate to Tables folder under the database (say AdventureWorksDW) from which you wan to get the Row Counts (Databases | AdventureWorksDW | Tables)
  • Object Explorer Details window shows a summary of all the Tables (present in the Tables folder under AdventureWorksDW database) as shown in the below screenshot.

Now, in the Object Explorer Details window, Right Click on the header and select Row Count from the context menu as shown in the below screenshot.

Now you will see the Row Count of all the Tables in the Object Explorer Details window as shown below.

Also, in the above displayed view, you can select multiple rows and copy the information into an excel or any other format for further reporting/auditing purposes, and you can add or remove the columns as required like we added the Row Count column above. This is a very hand approach for getting the Row Count from all the tables in a SQL Server database.

Note: This approach does not work for Views.

Take a look at the other Tips and Tricks in this Series on Tips, Tricks, Techniques, and Shortcuts to Improve Productivity, and Design and Coding Skills.

Advertisements

About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on June 1, 2012, in Shortcuts, SQL Client Tools, SQL Server, SSMS, SSMS Tips, Tips 'N' Tricks and tagged , , , , , , , , , . Bookmark the permalink. 4 Comments.

  1. This is really excellent and easy approach Datta.
    I use to do it in different ways…But i found that this is the easiest way which will reduce the effort when we work with huge DB’s..

    There is any approach like this to get counts for views?.I
    It will help us if we have view as source and table as destination.

  2. Thanks Naveen for the feedback.
    Unfortunately this approach does not work for Views. Since as you must already be aware that, Views are simply a layer of abstraction and they do not contain data in them (except for Indexed Views) and they are just a piece of SQL Script stored in a database and whenever you invoke a view, the view invocation is replaced with the view definition in the calling/invoking query.

    Hope that helps!

  1. Pingback: Tips ‘N’ Tricks – Tips, Tricks, Techniques, and Shortcuts to Improve Productivity, and Design and Coding Skills « Datta's Ramblings on Business Intelligence 'N' Life

  2. Pingback: Tips ‘N’ Tricks – SSMS – Write Queries Faster and Cleaner Using System and User Defined Query Templates « Datta's Ramblings on Business Intelligence 'N' Life

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: