Thursday, July 3, 2008

TSQL : Tables without Non Clustered Indexes

Some simple queries to quickly reveal tables where no non clustered indexes have been created.

SQL 2005+
SELECT SCHEMA_NAME(schema_id) ,name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasNonClustIndex') = 0
ORDER BY 1, 2

SQL 2000
SELECT name
FROM sysobjects
WHERE type = 'U'
AND id NOT IN (SELECT id
 FROM sysindexes  
 WHERE indid > 1)

No comments: