In this post, I am sharing one T-SQL script to find a Table which has more than 10 Indexes in SQL Server.
More Indexes on a Table, are creating a performance issue because Indexes also require dedicated CPU I/O for writing it into the Index Pages.
More Indexes will slow down the insertion or any other DML operation of that particular Table.
Below is a script to find out tables which are having more indexes:
You can change the value of @threshold.
DECLARE @threshold INT;
SET @threshold = 10;
[s].[name] + '.' + [t].[name] AS TableName
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON [t].[schema_id] = [s].[schema_id]
SELECT 1 FROM sys.indexes AS i
WHERE [i].[object_id] = [t].[object_id]
GROUP BY [i].[object_id]
HAVING COUNT(*) > @threshold