TSQL:
DECLARE @Index varchar(250) = 'NCI_INTERFACE_QUEUE_LOG_CUSTOM_0001'
SELECT
OBJECT_NAME(i.OBJECT_ID) TableName
,i.name AS IndexName
,i.type_desc IndexType
,SUM(PS.[used_page_count]) * 8 IndexSizeKB
,iu.user_seeks AS NumOfSeeks
,iu.user_scans AS NumOfScans
,iu.user_lookups AS NumOfLookups
,iu.user_updates AS NumOfUpdates
,iu.last_user_seek AS LastSeek
,iu.last_user_scan AS LastScan
,iu.last_user_lookup AS LastLookup
,iu.last_user_update AS LastUpdate
FROM sys.indexes i
INNER JOIN sys.dm_db_index_usage_stats iu ON iu.index_id = i.index_id AND iu.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=i.object_id
WHERE
1=1
and OBJECTPROPERTY(i.OBJECT_ID,'IsUserTable') = 1
and (@Index = '' or i.name Like '%' + @Index + '%')
GROUP BY OBJECT_NAME(i.OBJECT_ID),i.name,i.type_desc,iu.user_seeks,iu.user_scans,iu.user_lookups,iu.user_updates,iu.last_user_seek,iu.last_user_scan,iu.last_user_lookup,iu.last_user_update
|
|
|
|
|
|