THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences the Microsoft Data Platform

Find all the tables with no indexes at all

One important step of database maintenance is maintaining indexes, rebuilding or defragmenting them at  scheduled intervals.

This step is obvious to everyone, but has an “hidden” requirements that sometimes is underestimated. Indexes MUST exists!

I these last days I’ve been working on a “problematic” Smile  database server and one of the script that helped be to understand how big was the problem, is a simple script that shows, for each table, if it has a clustered index or nor and if it has nonclustered indexes.

Thanks to this script it’s very easy to find all the tables (along with the number of rows contained within) that doesn’t have an index at all, so you can explain to the customer that their performance problems are not related to poor index maintenance or badly written queries (which, of course, on a database without indexes are somehow expected…), but, in first place, by the total absence of indexes!

Here’s the script:

with cte as
        table_name =,   
        sys.indexes i
    inner join
        sys.objects o on i.[object_id] = o.[object_id]
        o.type in ('U')
        o.is_ms_shipped = 0 and i.is_disabled = 0  and i.is_hypothetical = 0
        i.type <= 2
), cte2 as
    cte c
    (count(type) for type_desc in ([HEAP], [CLUSTERED], [NONCLUSTERED])) pv
    [rows] = max(p.rows),
    is_heap = sum([HEAP]),
    is_clustered = sum([CLUSTERED]),
    num_of_nonclustered = sum([NONCLUSTERED])
    cte2 c2
inner join
    sys.partitions p on c2.[object_id] = p.[object_id] and c2.index_id = p.index_id
group by

As usual the script can be found also in the sys2 collection on CodePlex:

Published Monday, August 09, 2010 5:33 PM by Davide Mauri

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Rob Volk said:

If you just want the name of tables without indexes:


FROM sys.tables


OBJECTPROPERTY also has settings for clustered and non-clustered index checking (and a whole lot more).

August 9, 2010 10:56 AM

Davide Mauri said:

Cool!!!! I so into DMVS that I completely missed that. Thanks!

August 9, 2010 11:03 AM

Mohit K. Gupta said:

August 9, 2010 12:02 PM

Leave a Comment


About Davide Mauri

Davide Mauri - MCP, MCAD, MCDBA, MCT, MVP on SQL Server - has worked with SQL Server since version 6.5, and his interests cover the whole platform, from the Relational Engine to Analysis Services, from architecture definition to performance tuning. He also has a strong knowledge of XML, .NET and the Object Oriented Design principles, which allows him to have the correct vision and experience to handle development of complex business intelligence solutions. Having worked as a Microsoft Certified Teacher for many years, Davide is able to pass all his knowledge to his co-workers, allowing his team to deliver high-quality solutions. He currently works as a Mentor for SolidQ and can be found speaking in many Italian and internationals events.

This Blog


Privacy Statement