In a previous Index Basics post we went over the importance of having a Clustered Index on your tables, and we learned that SQL Server will automatically create a Clustered Index on the Primary Key column(s) of your table, unless you explicitly tell it not to.
But what about tables that don’t have Primary Keys? Is there a way to get a list of them and tell if they do or do not have a Clustered Index? Yes there is. SQL Server makes this information easily available via the sys.indexes DMV. In the below query, I also join on the sys.objects and sys.schemas DMVs to get the table and schema names.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE YourDB GO SELECT [TableName] = s.[name]+'.'+o.[name] FROM sys.indexes i INNER JOIN sys.objects o ON o.[object_id] = i.[object_id] INNER JOIN sys.schemas s ON s.[schema_id] = o.[schema_id] WHERE i.[type] = 0 -- '0' means 'Heap', which is a table with no clustered index AND o.[type_desc] = N'USER_TABLE' --only check user created tables ORDER BY [TableName] |
This query will return a list of tables that do not have a Clustered Index. It is database specific, so it only returns results from the database that you are running it against.
Nice, short, to-the-point article. Thanks for taking the time to post it.
To extend the lesson and simplify the code, we can make use of some pretty cool built in functions. We don’t need to join to sys.objects or sys.schemas to come up with the object name or the schema.
SELECT TableName = OBJECT_SCHEMA_NAME(object_id)
+ ‘.’
+ OBJECT_NAME(object_id)
FROM sys.indexes
WHERE index_id = 0 –Only on HEAPS (Tables with no clustered Index)
AND OBJECTPROPERTY(object_id,’IsUserTable ‘) = 1
ORDER BY TableName
;
Thanks Jeff!
Yeah, I considered the built in functions, but I tend to try to avoid using any functions in my queries whenever possible. Especially when returning an unknown, possibly large, set of records. The developer in me cringes at the thought of forcing SQL into row-by-row operations by using functions.
I’m sure the built in SQL Server functions are highly optimized, but my DBA-OCD just won’t let me do it! 😉