Tag Archives: Clustered Index

I-Tech SQL Server Script Series – Listing Clustered Indexes on All Tables

A good general rule for creating tables is to always have a clustered index. The clustered index defines how data is physically ordered on the disk – and that’s why there can only be one clustered index on a table (a table without a clustered index is a HEAP). Even though it’s a good rule to have a clustered index there are common scenarios when you won’t have one on a table. A typical example is a load or import table, but even then it may have a clustered index applied when the load is complete.

When investigating a database it is helpful to understand the clustered indexes. The chosen clustered index can have a large impact on performance. Understanding the clustered indexes is especially crucial since every non-clustered index on a table relies on the clustered index.

SQL Server stores object information, including indexes, in system views. Table information is available in sys.objects and the associated indexes are in sys.indexes. This script list all of the tables in the current database and their associated clustered index. If the table doesn’t have a clustered index it will have NULL for the IndexName. You can use SSMS to look at indexes individually, but listing them via SQL makes it easy to see all of them at once.

SQL Script

SELECT
	@@SERVERNAME		ServerName
	,DB_NAME()			DatabaseName
	,ss.name			SchemaName
	,so.name			TableName
	,si.name			IndexName
	,sc.name			ColumnName
	,st.name			ColumnType
	,CASE
		WHEN st.is_user_defined = 0 THEN st.name
		WHEN st.is_table_type	= 1	THEN AT.name
		WHEN st.is_user_defined = 1	THEN st2.name
	END ColumnBaseType
	,CASE 
		WHEN sc.max_length = -1 THEN 0
		WHEN st.name IN ('nchar','nvarchar') THEN sc.max_length	/ 2
		WHEN st2.name IN ('nchar','nvarchar') THEN sc.max_length	/ 2
		ELSE sc.max_length
	END	ColumnMaxLength
FROM sys.objects so
	INNER JOIN sys.schemas SS
		ON SO.schema_id = SS.schema_id
	LEFT JOIN sys.indexes si
		ON si.object_id = so.object_id
		AND si.type = 1	--clustered index
	LEFT JOIN sys.index_columns sic
		ON si.object_id = sic.object_id
		AND si.index_id = sic.index_id
	LEFT JOIN sys.columns sc
		ON sic.object_id = sc.object_id
		AND sic.column_id = sc.column_id
	LEFT JOIN sys.types st
		ON sc.user_type_id = st.user_type_id
	LEFT JOIN sys.types st2
		ON st.system_type_id		= st2.system_type_id 
		AND st2.system_type_id		= st2.user_type_id
		AND st.is_user_defined		= 1
	LEFT JOIN sys.assembly_types AT
		ON ST.system_type_id	= AT.system_type_id
		AND ST.user_type_id		= AT.user_type_id
WHERE so.type = 'u'
ORDER BY so.name
	,sic.Index_Column_ID

Script Download

The script is located here – INDEXES.Clustered.Indexes

It is also located on our script page – I-Tech SQL Server Scripts