SDU Tools: List User Heap Tables in SQL Server

It’s common advice that most SQL Server tables should have a clustered index. There are some exceptions to this but it’s a pretty general rule, and if in doubt, you should follow it. (Note that this is not the same as having a primary key).

I regularly come across tables without clustered indexes for all the wrong reasons. So, in our free SDU Tools for developers and DBAs, we added a tool that can look for user tables that don’t have a clustered index. No surprise, it’s called ListUserHeapTables because a table without a clustered index is a heap.

It takes three parameters:

@DatabaseName sysname - the database to look into
@SchemasToList nvarchar(max) - a comma-delimited list of schemas to check (or ‘ALL’)
@TablesToList nvarchar(max) - a comma-delimited list of table s to check (or ‘ALL’)

This procedure was added in an early version of SDU Tools but in version 27, we’ve made it more useful. As well as the schema name and table name, it now provides a page count, and a forwarded record count.

Forwarded records occur when the heap has nonclustered indexes and the underlying row is modified and moves to a different location. They avoid the need to update all the related indexes as well.

Find out more

You can see it in action in the main image above, and in the updated video here:

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

2026-04-08