Sunday, May 17, 2020

Tables with more than 5 indexes

 

Query to Retrieve Index Table List

In this blog post, we'll look at a query that returns a list of tables in a database that have more than a specified number of indexes. This can be helpful for identifying tables with an unusually high number of indexes, which may be a sign of inefficiency.

The query is as follows:


DECLARE @threshold INT;
SET @threshold = 5;

SELECT 
   [Table] = s.[Name] + N'.' + t.[Name] 
FROM [sys].tables t
   INNER JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
   SELECT 1 FROM [sys].indexes i
   WHERE i.[object_id] = t.[object_id]
   GROUP BY i.[object_id]
   HAVING COUNT(*) > @threshold
);

Explanation:

  1. The query uses a threshold value (@threshold) that you can customize. In this case, the threshold is set to 5.
  2. The SELECT statement retrieves the table names along with their schema name.
  3. The WHERE EXISTS condition filters out tables that have fewer than the specified number of indexes.

This can be particularly useful in cases where you are analyzing database performance or trying to optimize query speed by reviewing indexing strategies.

Additional References:

at

Popular Posts