I'd like to share with you this script that returns name of tables that do not have Clustered Index defined on.You may ask, why do we need to create a Clustered Index on the table at all? Well,I think it is a subject for another blog , my experience is that every table should have clustered index.
--SQL Server 2005 (SP2)
SELECT name AS object_name FROM sys.objects WHERE name NOT IN
(
SELECT o.name AS object_name
FROM sys.indexes i
JOIN sys.objects o
ON i.object_id = o.object_id
WHERE objectproperty(o.object_id,'IsMSShipped') =0
AND I.type=1 ---Clustered Index
) AND objectproperty(sys.objects.object_id,'IsMSShipped') =0 ---Only user objects
AND objectproperty(sys.objects.object_id,'IsTable') =1 ---Only tables
ORDER BY name
Monday, January 21, 2008
Subscribe to:
Post Comments (Atom)
1 comment:
The following query should return the same results:
SELECT OBJECT_NAME(object_id) as object_name
FROM sys.indexes WHERE type=0
AND objectproperty(object_id,'IsMSShipped')=0
AND objectproperty(object_id,'IsTable')=1
ORDER BY object_name
Razvan
Post a Comment