SQLTeam.com | Weblogs | Forums

How to know if a table has a non-unique clustered index?

sql2008r2

#1

Hi,

Give a user table ‘MyTable’. How to know whether the table
contains a non-unique clustered index by using SQL query?

Thanks


#3

I misread the question, you can simply find that out using system table sys.indexes. There is one is_unique column which tells if index is unique or not.


#4

using sys.indexes you can find if the table has a non-unique clustered indexes or not see here: https://msdn.microsoft.com/en-us/library/ms173760.aspx


#5
-- SQL Server list all indexes in database - SQL Server database schema 
USE [DBName];
SELECT   SchemaName = schema_name(schema_id), 
         TableName = object_name(t.object_id), 
         ColumnName = c.name, 
         [Unique] = CASE 
                      WHEN i.is_unique = 1 THEN 'Yes' 
                      ELSE 'No' 
                    END, 
         [Clustered] = CASE 
                         WHEN i.index_id = 1 THEN 'C' 
                         ELSE 'NC' 
                       END, 
         Ordinal = key_ordinal,
         IndexName = i.Name 
FROM     sys.indexes i 
         INNER JOIN sys.tables t 
           ON i.object_id = t.object_id 
         INNER JOIN sys.index_columns ic 
           ON ic.object_id = t.object_id 
              AND ic.index_id = i.index_id 
         INNER JOIN sys.columns c 
           ON c.object_id = t.object_id 
              AND ic.column_id = c.column_id 
			  --where i.name = 'index_name' -- use for specific index checking!
ORDER BY SchemaName, 
         TableName, 
         IndexName, 
         key_ordinal;