How to fetch the row count for all tables in a SQL SERVER database

How to Fetch the Row Count for All Tables in a SQL Server Database

So you want to be able to determine if there is any data (i.e. row count) in any of the tables in your SQL Server database, and if so, take appropriate action. You're in luck! In this blog post, we will walk you through a simple and efficient solution to this problem.

The Problem: Checking Row Count in SQL Server Tables

Imagine you have a Microsoft SQL Server database and you want to check if any tables within this database have any rows. This could be useful when, for instance, you want to re-incarnate the database only if there is no existing data.

The Solution: Using SQL Scripts to Fetch Row Count

To accomplish this task, you can use a SQL script that queries the sys.syspartitions system view to fetch the row count for all tables in your database. Here's an example of such a script:

SELECT 
    OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
    OBJECT_NAME(object_id) AS TableName,
    p.rows AS RowCount
FROM 
    sys.syspartitions p
WHERE 
    index_id < 2
    AND OBJECT_NAME(object_id) <> 'sysdiagrams'
ORDER BY 
    p.rows DESC;

Let's break down this script:

• We start by selecting the OBJECT_SCHEMA_NAME and OBJECT_NAME functions to retrieve the schema and table names respectively.

• We use the sys.syspartitions system view to get the row count (p.rows) for each table.

• The index_id < 2 condition ensures that we only count rows for tables, not indexes.

• We exclude the sysdiagrams table, as it is used by SQL Server for database diagrams and might not contain relevant data.

• Finally, we order the results by the row count in descending order.

Running this script will provide you with a list of tables, their respective schemas, and the row count for each table.

Common Issues and Troubleshooting

Issue 1: Tables Missing in the Result

If you notice some tables missing from the result, it could be due to the fact that they do not have any rows. By default, SQL Server does not create an entry in sys.syspartitions for tables with no rows.

Issue 2: Invalid Object Name Error

If you encounter an "Invalid object name" error when running the script, it could mean that you're executing the script in the wrong database context. Make sure you are connected to the correct database or specify the database name explicitly in the script.

Conclusion and Call to Action

Now you have a handy SQL script to fetch the row count for all tables in your SQL Server database. This script can help you identify tables with existing data and make decisions based on that information. Whether you're planning to re-incarnate the database or simply want to understand the state of your data, this solution has got you covered!

Try running the script in your SQL Server database and see the results for yourself. If you have any questions or face any issues along the way, feel free to leave a comment below. Happy coding! ✨🚀