.::: List Index, size Index, unusage index, duplicate index & drop Index in SQL Server :::.

1. list index all

use [AdventureWorks2019]
go

select @@servername as ServerName, db_name() as [DBName],object_id, name from sys.indexes
where object_id in (select object_id from sys.objects )

 

2. size index all
 
use [AdventureWorks2019]
go

SELECT @@servername as ServerName,db_name() as [DBName],
 i.[name] AS IndexName
    ,SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
    AND s.[index_id] = i.[index_id]
where i.[name] ='AK_Address_rowguid'
GROUP BY i.[name]
ORDER BY i.[name]
GO
 
 

3. check unsuage index

use [AdventureWorks2019]
go
SELECT DISTINCT @@servername as ServerName,db_name() as DBName,
    objects.name AS Table_name,
    indexes.name AS Index_name,
    indexes.type_desc AS Index_type,
    STRING_AGG(COLUMNS.NAME,',') AS Index_columns,
    --STRING_AGG(COLUMNS.NAME,',') AS Index_columns,
    STRING_AGG(COLUMNS.NAME,',') AS  Include,
    '' AS Partition,
    dm_db_index_usage_stats.user_seeks AS No_of_seeks,
    dm_db_index_usage_stats.user_scans AS No_of_scans,
    dm_db_index_usage_stats.user_updates As No_of_updates,
    dm_db_index_usage_stats.user_lookups As No_of_lookups,
     '' AS Comment
     --stuff((select DISTINCT ','+columns.name from sys.columns a where a.object_id = dm_db_index_usage_stats.object_id for XML PATH('')), 1 ,1, '') cOLUMN_NAMES

FROM
    sys.dm_db_index_usage_stats
    INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
    INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id
    INNER JOIN sys.index_columns  ON index_columns.object_id = indexes.object_id and index_columns.index_id = indexes.index_id
    INNER JOIN sys.columns  ON columns.object_id = index_columns.object_id and columns.column_id = index_columns.column_id
    AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID

-- WHERE         objects.name  IN  ('dbo.barang') and index_columns.is_included_column =1
--AND
    --dm_db_index_usage_stats.user_lookups = 0
    --AND
   -- dm_db_index_usage_stats.user_seeks = 0
   -- AND
   -- dm_db_index_usage_stats.user_scans = 0
GROUP BY
 objects.name ,
 indexes.name ,
indexes.type_DESC,
dm_db_index_usage_stats.user_seeks ,
dm_db_index_usage_stats.user_scans ,
dm_db_index_usage_stats.user_updateS,
dm_db_index_usage_stats.user_lookups
ORDER BY
  indexes.name DESC


  --select * from sys.index_columns
  --select * from sys.indexes
  --select * from sys.columns
  

4. if want to delete index

drop AK_Customer_rowguid on Customer;