query to find the list of tables with clustered index

Question

_{Tuesday, June 19, 2012 5:06 PM}

In our database we found a table that was having clustered index on it , but not unique clustered index.Because of this , duplicates were inserted into that table .

Now, we want to get the list of all the tables in that databse that have clustered index on that (not unique clustered index).

I have using few queries but that does not work .

Can someone help me with this?

All replies (4)

_{Tuesday, June 19, 2012 7:57 PM ✅Answered | 1 vote}

I've used the following.

SELECT 
    t.name AS table_name, 
    i.type_desc AS index_type, 
    i.is_unique AS is_unique_index
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
INNER JOIN sys.indexes AS i
    ON t.object_id = i.object_id
WHERE i.type_desc = 'CLUSTERED';

_{Tuesday, June 19, 2012 5:09 PM | 1 vote}

Hello,

Here you can find a script to get all tables without a CI = heap table: http://gallery.technet.microsoft.com/scriptcenter/List-all-heap-tables-7ffaea35

Just inverse the logic and may modify the query to get all tables with non-unique CI.

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

_{Tuesday, June 19, 2012 5:09 PM}

Your problem is lack of a primary key, not clustered indexes

Chuck Pedretti | Magenic – North Region | magenic.com

_{Tuesday, June 19, 2012 6:32 PM | 2 votes}

This is a simple requirement where we would like to find out if a particular table has a clustered index or not. 

    Select   i.TABLE_NAME,
                Case objectProperty(object_id(i.TABLE_NAME), 'TableHasClustIndex')
                When 0 then 'No'
                When 1 then 'Yes'
                End as [Has Clustered Index]
    From    INFORMATION_SCHEMA.TABLES i
    Where  objectProperty(object_id(i.TABLE_NAME), 'IsUserTable') = 1
    Order by [Has Clustered Index] , i.TABLE_NAME asc

But you must know this

1. Primary keys may not clustered index
2. Only one Clustered index per table you can create.

OR Vs XOR