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 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
(
    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

Director of Software Development & Cloud Infrastructure @ Sensoria, an innovative smart garments and wearable company. After more than 15 year playing with the Microsoft Data Platform, with a specific focus on High Performance databases, Business Intelligence, Data Science and Data Architectures, he's now applying all his skills to IoT, defining architectures to crunch numbers, create nice user experiences and provide meaningful insights, all leveraging Microsoft Azure cloud. MVP on Data Platform since 2006 he has a very strong background development and love both the ER model and OO principles. He is also a fan of Agile Methodology and Automation, which he tries to apply everywhere he can, to make sure that "people think, machines do".

This Blog

Syndication

Privacy Statement