SQL Server All Indexes Script

,

With the script I have prepared, you will see the b-tree in our tables and the new index types where the ColumStore Indexes are and how much they are.
Most of your improvement work will also benefit your work.

Yusuf KAHVECI

yusufkahveci@sqlturkiye.com

www.sqlturkiye.com

Thanks

SELECT
	[schema_name] = s.name
   ,table_name = o.name
   ,MAX(i1.type_desc) Clustered_Index_or_Heap
   ,MAX(COALESCE(I2.NonClustered_Index, 0)) NonClustered_Index
   ,MAX(COALESCE(I4.NC_Column_Store_Index, 0)) NC_Column_Store_Index
   ,MAX(COALESCE(I3.Other_Index, 0)) Other_Index
FROM sys.objects o
INNER JOIN sys.schemas s
	ON o.[schema_id] = s.[schema_id]
LEFT JOIN sys.indexes i1
	ON o.OBJECT_ID = i1.OBJECT_ID
		AND i1.TYPE IN (0, 1, 5)
LEFT JOIN (SELECT
		object_id
	   ,COUNT(Index_id) NonClustered_Index
	FROM sys.indexes
	WHERE type = 2
	GROUP BY object_id) I2
	ON o.OBJECT_ID = i2.OBJECT_ID
LEFT JOIN (SELECT
		object_id
	   ,COUNT(Index_id) Other_Index
	FROM sys.indexes
	WHERE type IN (3, 4, 7)
	GROUP BY object_id) I3
	ON o.OBJECT_ID = i3.OBJECT_ID
LEFT JOIN (SELECT
		object_id
	   ,COUNT(Index_id) NC_Column_Store_Index
	FROM sys.indexes
	WHERE type = 6
	GROUP BY object_id) I4
	ON o.OBJECT_ID = i4.OBJECT_ID
WHERE o.TYPE IN ('U')
GROUP BY s.name
		,o.name
ORDER BY schema_name, table_name

Rate

2.83 (6)

Share

Share

Rate

2.83 (6)