THE SQL Server Blog Spot on the Web

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

Davide Mauri

A place for my thoughts and experiences on SQL Server, Business Intelligence and .NET

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
(
    select
        table_name = o.name,   
        o.[object_id],
        i.index_id,
        i.type,
        i.type_desc
    from
        sys.indexes i
    inner join
        sys.objects o on i.[object_id] = o.[object_id]
    where
        o.type in ('U')
    and
        o.is_ms_shipped = 0 and i.is_disabled = 0  and i.is_hypothetical = 0
    and
        i.type <= 2
), cte2 as
(
select
    *
from
    cte c
pivot
    (count(type) for type_desc in ([HEAP], [CLUSTERED], [NONCLUSTERED])) pv
)
select
    c2.table_name,
    [rows] = max(p.rows),
    is_heap = sum([HEAP]),
    is_clustered = sum([CLUSTERED]),
    num_of_nonclustered = sum([NONCLUSTERED])
from
    cte2 c2
inner join
    sys.partitions p on c2.[object_id] = p.[object_id] and c2.index_id = p.index_id
group by
    table_name

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

http://sys2dmvs.codeplex.com/

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

Comments

 

Rob Volk said:

If you just want the name of tables without indexes:

SELECT name

FROM sys.tables

WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasIndex')=0

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

(required) 
(required) 
Submit

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

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement