Sometimes you just want to get a quick row count for a specific table – other times you may want to see a list of tables order by size or row count or perhaps you are monitoring table row counts, whatever it is the chances are you have a script or various scripts stashed away and maybe like me you end up re writing parts of them to suit your requirements.
I got bored of that game so I decided to write a stored proc to make things easier.
I know that we can just go and do a Select COUNT from whatever table or right click > properties, that’s fine when you are only dealing with a handful of tables and need an exact count but why read all those data pages and risk blocking other users when the chances are we probably only need an approximate count.
We can can leverage some DMVs to help us get this information, the row counts and size information come from sys.partitions and sys.allocation_units
Please be aware that sys.partitions will provide an approximate row count and sys.allocations will reflect the pages allocated but with this caveat from the books online page:
When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sys.allocation_units immediately after dropping or truncating a large object may not reflect the actual disk space available.
The stored procedure code can be found on our Github page.
Here is an overview of the code.
Parameters:
@Databasename – Pass database name or leave NULL for current DB (Default: NULL)
@Schemaname – Pass Schema name or leave NULL for all schemas (Default: NULL)
@Tablename – Pass Table name or leave NULL for all tables (Default: NULL)
@Sortorder – Valid options ‘Schema’, ‘Table’, ‘Rows’, ‘Delta’, ‘Size’ these options as DESC (Default: Schema ASC, Tablename ASC)
@Top – Specify an INT value to restrict the number of rows returned or NULL for all rows (Default: NULL)
@Interval – Specify an INT value in seconds for some extra delta columns so show changes during the duration.
@Getsizes – Include table sizes column , this can impact performance of the proc against databases with a lot of tables so it defaults to 0.
Here are some commands that you can use with sp_Tablecount to get results within your current database context:
Get row counts for all tables in the database:
EXEC sp_Tablecount;
Get row counts or all tables in a specific schema;
EXEC sp_Tablecount @Schemaname = 'Reporting';
Get row counts for a specific table:
EXEC sp_Tablecount @Schemaname = 'dbo', @Tablename = 'Posts';
We can also run with an Interval so we can see some deltas against the table/s using @Interval
EXEC sp_Tablecount @Interval = 5, @Sortorder = 'Delta';
In a separate query window I ran a delete against the Posts table inside of a transaction just to show a change in the data.
Highlighted above is a delta column for the total rows so we can see if there were any Inserts or deletes per table during the @Interval in seconds.
We can also do the same thing as above but also include sizes:
sp_Tablecount @Getsizes = 1, @Interval = 5, @Sortorder = 'Delta';
@Getsizes = 1 may take some time if you are returning lots of tables! just keep this in mind – it defaults to 0.
Return counts for all tables ordered by Size:
EXEC sp_Tablecount @Getsizes = 1, @Sortorder = 'Size';
And in addition to all of the above you can specify @Sortorder with any one of the following accepted values:
NULL – (Default: Schema ASC, Tablename ASC)
‘Schema’ – Sort by Schema name ASC, Tablename ASC
‘Table’ – Sort by Tablename ASC
‘Rows’ – Sort by TotalRows DESC
‘Delta’ – Sort by TotalRows_Delta DESC
‘Size’ – Sort by SizeMB DESC
Thanks for reading!