Give a user table ‘MyTable’. How to know whether the table contains a non-unique clustered index by using SQL query?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Create a simple table with a non-unique clustered index:
CREATE TABLE dbo.TestCluster ( TestClusterID INT NOT NULL ); CREATE CLUSTERED INDEX CX_TestCluster --note there is no "UNIQUE" keyword here ON dbo.TestCluster(TestClusterID);
This query shows if the given index is unique or not:
SELECT SchemaName = s.name , TableName = o.name , IndexName = i.name , IndexType = i.type_desc , IsUnique = i.is_unique FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE s.name = 'dbo' AND o.name = 'TestCluster' AND i.type_desc = 'CLUSTERED';
It's always vitally important that you specify/show the schema, as I have done above, since it very easy that objects with the same name might exist in multiple schemas.
Assuming you are running SQLServer:
SELECT OBJECT_NAME(object_id) AS TableName , name AS IndexName , type_desc AS IndexType , CASE WHEN is_unique = '1' THEN 'UNIQUE' WHEN is_unique = '0' THEN 'NON UNIQUE' END AS Index_Desc FROM sys.indexes WHERE type_desc = 'CLUSTERED' AND OBJECT_NAME(object_id) = 'MyTable';
Just one other method, but not better than Max Vernon's code, which gives the schema name too.