Share via

How to get row counts for all Tables

Introduction

A good SQL Server administration requires verification and routine monitoring for growth size of each database.

This information helps us define preventive actions, such as adjustments in data partitioning and also in several business decisions.

In this article, we will discuss how to quickly get the amount of records stored for each table on a database in order to facilitate data collection for later use in an analysis of growth of their databases and their respective tables.

There may be various scenarios when you need to get the Row Counts of all the tables in SQL. For example: you may have a server and need to find out which table holds the maximum number of rows and how many tables are with no records in it. There are many ways available in SQL server to get the rows count of each table in SQL server but it would be wise if we find the row count of the table with out writing a query against that table.

Count the number of rows in each Table

So we can list all tables of a SQL Server database, identifying the size to each table we need to run something other than a COUNT method on SELECT statement.

There are two ways to obtain this information:

  • Using system tables with Transact-SQL (T-SQL);
  • Querying the "Tables" folder on "Object Explorer Details" panel;

We will demonstrate how these features can be used.

Using T-SQL

When we need to check the number of rows of each table on a database, we have to remember there two conditions to collect this information through T-SQL:

  • Get the number of rows that compose the Clustered Index (Primary Key) of a table, or;
  • Get the number of rows that compose a Heap table (sem índice Clusterizado);

Such information can be found through of the sys.sysindexes view, where only the Clustered Index (IndId = 1) and Heap tables (IndId = 0) will be considered.

The sys.sysobjects table helps indicating the table name for each index and targeting the WHERE clause just tables created by users (type = 'U').

See this output SQL script in the image below

See this T-SQL script in the below

USE YourDatabase
GO

SELECT
       OBJ.Name AS TABLES, IDX.Rows AS ROWS_COUNT
FROM
       sys.sysobjects AS OBJ
INNER JOIN
       sys.sysindexes AS IDX
ON
       OBJ.id = IDX.id
WHERE
       type = 'U'
AND
       IDX.IndId < 2
ORDER BY
       IDX.Rows DESC
GO

 Warning

The sys.indexes view was originated as a system table in SQL Server 2000 and is now included as a view from the SQL Server 2005 or later. This feature may be removed in a future version of Microsoft SQL Server.

Using the Object Explorer Details panel

Query the number of rows in each table through SSMS is a simple task, just follow these steps:

  • Select the **Object Explorer **panel;
  • Click to expand until the desired database;
  • Select the **Tables **folder;

See this output SQL script in the image below

  • In the Tables folder select the **Object Explorer **panel, so let's see the Object Explorer Details panel;
  • To access Object Explorer Details panel you can just click the "View" menu on SSMS and select the **Object Explorer Details **item;

See this output SQL script in the image below

Conclusion

You have more than one possibility to get information about our tables on a SQL Server database and this is a very useful resource, especially because these alternatives to get desired data are simple and quick to implement.

You can choose the options for use by a T-SQL or a resource as a SSMS panel, depends only on their need for data collection and storage for later analysis and decision to everyone involved in the use of the information and the structure of a SQL Server database.

References

See Also

Other Languages

This article was awarded the silver medal in the TechNet Guru of June/2014