Friday, 1 June 2018

TSQL : Clustered Indexes, Identities & Primary Keys

I'm Currently looking at a large database where there are
  • HEAPS (tables without a clustered index)
  • IDENTITY columns that could serve as Primary Keys (but aren't)
  • PRIMARY KEYS that have been defined as non-clustered (someone has scripted them out and ignored the default clustered status of a PK).
Anyway, this is coming in useful ...

SELECT   SCHEMA_NAME(schema_id) AS SchemaName
  ,name AS TableName
  ,OBJECTPROPERTY(object_id,'TableHasClustIndex') HasClusteredIndex
  ,OBJECTPROPERTY(object_id,'TableHasIdentity') HasIdentity
  ,OBJECTPROPERTY(object_id,'TableHasprimarykey') HasPrimaryKey
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0

No comments: