SQL Server All Indexes Script

, 2018-06-07 (first published: )

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)

Related content

SQL Server Indexes: The Basics

Indexes directly affect the performance of database applications. This article uses analogies to describe how indexes work. The estimated execution plan feature of the Query Window is utilized to compare the performance of two queries in a batch.

2007-12-04

5,686 reads

Indexed Views With Outer Joins

SQL Server 2000 has indexed views, which can greatly improve database performance. However there are a number of restrictions on building the view, including the restriction against outer joins. So how can this work? New author Jean Charles Bulinckx brings us a technique that can help you get around this restriction.

3.5 (6)

2005-05-19

14,161 reads

Clustering for Indexes

There is nothing spectacular about using indexes per say. However, on many occasions I have come across a variety of SQL coders that never consider validating that the index they think they are using is efficient or even being used at all. We can all put indexes on the columns that we think will be required to satisfy individual queries, but how do we know if they will ever be used. You see, if the underlying table data is constructed, contains, or is ordered in a particular way, our indexes may never be used. One of the factors around the use of an index is its clustering factor and this is what this article is about.

2004-12-23

3,412 reads