Get all Index for a table…

EXEC sys.sp_helpindex @objname = N'tblXXXXX' 

Or query from the indexes system table…

SELECT 
TableName = t.name,
IndexName = ind.name,
IndexId = ind.index_id,
ind.*
FROM
sys.indexes ind
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
WHERE
t.name = 'tblXXXXX'

For those tables with multiple partitions…

SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
WHERE o.name LIKE 'tblXXXXX'
Last modified: April 10, 2019

Author

Comments

Write a Reply or Comment