Primary Keys List

This script is very useful in case we need to check if all our primary keys are also clustered. If not, then RID Lookup (Heap) will be used instead of Clustered Index Seek with all consequences related to it.

SELECT
	[s].[name] [Schema_Name],
    [o].[name] AS [Object_Name],
	[o].[type_desc] [Object_Type],
	[ix].[name] AS [Primary_Key_Name],
    [ic].[index_column_id] AS [Column_Id],
    [c].[name] AS [Column_Name], 
	[ix].[type_desc] [Index_Type]
FROM [sys].[objects] [o]
	INNER JOIN [sys].[schemas] [s] ON [s].[schema_id] = [o].[schema_id]
    INNER JOIN [sys].[indexes] [ix] ON [o].[object_id] = [ix].[object_id] AND [ix].[is_primary_key] = 1
    INNER JOIN [sys].[index_columns] [ic] ON [ic].[object_id] = [ix].[object_id] AND [ic].[index_id] = [ix].[index_id]
    INNER JOIN [sys].[columns] [c] ON [ix].[object_id] = [c].[object_id] AND [c].[column_id] = [ic].[column_id]
ORDER BY [s].[name], [o].[name], [c].[Column_Id]