An important ingredient in database designing is creating, maintaining and rebuilding indexes after some specified intervals. One main aspect of database tuning is to apply indexes. Index must exist in databases otherwise their would be a lot of performance issues.
While searching across internet, i came across a script that will list all the tables in my database and also show how many indexes are created/applied on those table wither clustered or non clustered.
Here’s the script:
with cte as
table_name = o.name, �
sys.objects o on i.[object_id] = o.[object_id]
o.type in (‘U’)
o.is_ms_shipped = 0 and i.is_disabled = 0 and i.is_hypothetical = 0
i.type <= 2
), cte2 as
(count(type) for type_desc in ([HEAP], [CLUSTERED], [NONCLUSTERED])) pv
[rows] = max(p.rows),
is_heap = sum([HEAP]),
is_clustered = sum([CLUSTERED]),
num_of_nonclustered = sum([NONCLUSTERED])
sys.partitions p on c2.[object_id] = p.[object_id] and c2.index_id = p.index_id