Friday, April 29, 2011

TSQL : Identity Columns that do little else!

Tables with Identity Columns, but NO clustered index
SELECT   SCHEMA_NAME(schema_id) AS SchemaName 
  ,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
  AND OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0
ORDER BY 1, 2

Tables with Identity Columns, but NO primary key
SELECT   SCHEMA_NAME(schema_id) AS SchemaName 
  ,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
  AND OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
ORDER BY 1, 2

No comments: