Monday, January 21, 2008

Tables that do not have Clustered Index

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 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


Razvan Socol said...

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


aiya said...

Office 2010is powerful!
Microsoft Office 2010is the best software in the world.
Microsoft wordis so great!
Office 2007makes life great!
Many people likeMicrosoft Office.
Microsoft Office 2007is welcomed by the whole world.
Office 2007 keyis available here.
Office 2007 downloadis on sale now!
Office 2007 Professionalbring me so much convenience.
Outlook 2010 make life wonderful!
Microsoft outlook is my love.
Microsoft outlook 2010 is convenient!
Windows 7 is convenient!