Monday, September 04, 2006

Tables with No index

Similar to my previous post but gets tables with no index on them.

SELECT distinct o.name from sysobjects o
left outer join (select distinct object_name(id) as TableName from sysindexes where indid between 1 and 249) t
on t.TableName = o.name
where o.xtype = 'u' and t.TableName is null

Tables with no Clustered Index

Have used the following to see tables in database without clustered index.
SELECT o.name from sysobjects o
left outer join (select distinct object_name(id) as TableName from sysindexes where indid = 1) t
on t.TableName = o.name
where o.xtype = 'u' and t.TableName is null