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

sql

database-performance

sql-scripts

database-maintenance

byAlex Kataev·Oct 11, 2024

Here's a quick and dirty way to fetch row counts for all tables, straight from the SQL Server drawer. We’ll exploit sys.tables to wink at the table names and cuddle sys.partitions for the aggregated rows:

SELECT t.NAME AS TableName, SUM(p.rows) AS RowCount
FROM sys.tables t
INNER JOIN sys.partitions p ON t.object_id = p.object_id 
WHERE p.index_id IN (0, 1) -- Only heap/clustered index makes the cut here, non-clustered indexes need not apply
GROUP BY t.NAME
ORDER BY RowCount DESC;

No more, no less! Filters out system tables and avoids counting row duplicates from non-clustered indexes.

Go deep or go home: Performance and accuracy

SQL can sometimes feel like herding cats, more so with larger databases. Here's the lowdown on why the above query makes your life easier:

sys.tables and sys.partitions: Querying these system views comes with fewer strings attached compared to others — less filters and more speed!
WHERE p.index_id IN (0, 1): Think of this as the VIP list at the club. Filtering out the non-clustered indexes results in an exclusive and actual row count party.
GROUP BY t.NAME: This groups our VIPs by table name, so no table gets to cut the line twice.
ORDER BY RowCount DESC: Lets genuine SQL grease monkeys quickly spot the tables hogging all the rows.

No stumbling blocks: Avoiding typical bottlenecks

We've all coded something quick and dirty that came back to haunt us later. Here are quick fixes that ensure your SQL doesn't morph into Frankenstein's monster:

Only user's darlings: Filter tables to count by adding AND t.type = 'U', ensuring you're counting user-created, not the system's miscreations.
No trespassing: You're a SQL enthusiast, not an impostor! Always ensure you're querying in the correct database context.
Keep it fresh: For volatile environments, consider flying high with transaction isolation levels to keep row counts from aging like milk.

The beauty of clean-up: Post-quarry maintenance

SQL scripts are like house guests. They shouldn't leave a mess behind! Keep your database clean after your row count dinner:

Drop temporary tables: Who needs unnecessary baggage? If you've created temporary tables like #counts, kick them to the curb with DROP TABLE #counts.
Locksmith: Be aware of locks your query might have locked and left behind. They might block future fun in high concurrency environments.

Ready for prime time: Querying in larger databases

Handling larger databases requires a bigger toolbox. Consider these methods when you're in the big league:

sp_MSForEachTable: This undocumented stored procedure is like a secret handshake among SQL aficionados. But remember, with great power comes great responsibility. Use it wisely!
ASYNC_STATISTICS_UPDATE: Turn this on for a potential nitro boost to statistics and countdown to swift row count retrieval.

No gate-crashers allowed: Ensuring user table exclusivity

Make sure no unwanted system objects are crashing your row count party:

Exclude gate-crashers: System tables can be party poopers. Check the guest list thoroughly using is_ms_shipped attribute or view the OBJECTPROPERTY.
Filter with OBJECT_TYPE: Add the trustworthy bouncer AND OBJECT_TYPE(t.object_id) = 'U' to exclude gate-crashing tables from your query.