CODEWITHSUNDEEP
sqlsql-server
Conrad Jagger
Conrad Jagger

Reputation: 2333

SQL Server - How to find if clustered index exists

We got 500+ tables and want to identify which tables doesn't have primary keys. Because creating index on large table will help to improve performance.

Required Command - to identify table which are HEAPS (as they dont have clustered index)

Regards

Upvotes: 5

Views: 10046

Answers (1)

Martin Smith
Martin Smith

Reputation: 453707

SELECT OBJECT_NAME(object_id)
FROM sys.indexes 
WHERE index_id=0 
  AND OBJECTPROPERTY(object_id, 'IsUserTable') = 1

Finds all heaps. This issue is orthogonal to whether or not a PK exists though. A heap can have a non clustered PK and a clustered index isn't necessarily the PK. To find tables without a PK you can use.

SELECT *
FROM sys.tables t
WHERE NOT EXISTS
(
SELECT *
FROM sys.indexes i
WHERE is_primary_key=1 AND i.object_id = t.object_id
)

Upvotes: 4

Related Questions