Getting table row counts with sp_Tablecount

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;

2019-10-29 14_24_38-SQLQuery3.sql - DESTINY_SQL02.StackOverflow2010 (DESTINY_adest (60))_ - Microsof

 

Get row counts or all tables in a specific schema;


EXEC sp_Tablecount @Schemaname = 'Reporting';

2019-10-29 14_28_34-SQLQuery3.sql - DESTINY_SQL02.StackOverflow2010 (DESTINY_adest (60))_ - Microsof

 

Get row counts for a specific table:


EXEC sp_Tablecount @Schemaname = 'dbo', @Tablename = 'Posts';

2019-10-29 14_29_27-SQLQuery3.sql - DESTINY_SQL02.StackOverflow2010 (DESTINY_adest (60))_ - Microsof.png

 

 

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.

2019-10-29 14_40_00-SQLQuery3.sql - DESTINY_SQL02.StackOverflow2010 (DESTINY_adest (60))_ - Microsof

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';

2019-10-29 14_56_53-SQLQuery3.sql - DESTINY_SQL02.StackOverflow2010 (DESTINY_adest (60))_ - Microsof

@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';

2019-10-29 15_50_58-SQLQuery3.sql - DESTINY_SQL02.StackOverflow2010 (DESTINY_adest (60))_ - Microsof

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!

4 thoughts on “Getting table row counts with sp_Tablecount

Add yours

  1. Although deprecated, sysindexes gives you the quickest answer for rowcount (rowcnt) and tablesize (dpages) add “WHERE indid < 2" to get table data

    Like

  2. Specifying an @Interval greater than 59 results in the following error on our SQL Server 2008 R2 box:

    Conversion failed when converting date and/or time from character string.

    You attempt to address this in your code, but your mitigation requires tweaking:

    –Delta maximum is 59 seconds
    IF (@Interval >= 60)
    BEGIN
    SET @Interval = 59;
    PRINT ‘@Interval was changed to the maximum value of 59 seconds’;
    END

    Like

Leave a Reply

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: