適用 SQL Server 2005 以上版本,輸出:索引名稱、資料表、索引欄位、欄位順序、排序方向、是否主鍵、是否唯一、Fill Factor 等。
DECLARE @TableName SYSNAME = N'cz201'; -- 修改為目標表名
SELECT
TableName = OBJECT_NAME(idx.[object_id]),
IndexName = idx.name,
IndexType = idx.type_desc,
ColumnName = col.name,
ColumnOrder = idxc.index_column_id,
SortDirection = CASE INDEXKEY_PROPERTY(idxc.[object_id], idxc.index_id, idxc.index_column_id, 'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
IsPrimaryKey = CASE WHEN idx.is_primary_key = 1 THEN N'✓' ELSE N'' END,
IsUnique = CASE WHEN idx.is_unique = 1 THEN N'✓' ELSE N'' END,
IgnoreDupKey = CASE WHEN idx.ignore_dup_key = 1 THEN N'✓' ELSE N'' END,
IsDisabled = CASE WHEN idx.is_disabled = 1 THEN N'✓' ELSE N'' END,
FillFactor = idx.fill_factor,
IsPadded = CASE WHEN idx.is_padded = 1 THEN N'✓' ELSE N'' END
FROM sys.indexes idx
INNER JOIN sys.index_columns idxc
ON idx.[object_id] = idxc.[object_id] AND idx.index_id = idxc.index_id
INNER JOIN sys.columns col
ON idxc.[object_id] = col.[object_id] AND idxc.column_id = col.column_id
INNER JOIN sys.objects obj
ON obj.[object_id] = idx.[object_id]
WHERE obj.type = 'U' -- 使用者資料表
AND obj.is_ms_shipped = 0 -- 排除系統表
AND obj.name = @TableName
AND idx.index_id > 0 -- 排除堆疊
ORDER BY idx.name, idxc.index_column_id;
SELECT
索引名稱 = a.name,
表名 = c.name,
索引欄位 = d.name,
欄位位置 = d.colid
FROM sysindexes a
JOIN sysindexkeys b ON a.id = b.id AND a.indid = b.indid
JOIN sysobjects c ON b.id = c.id
JOIN syscolumns d ON b.id = d.id AND b.colid = d.colid
WHERE a.indid NOT IN (0, 255)
AND c.name = 'message'; -- 指定表名
SELECT index_name, index_type, table_name
FROM user_indexes
WHERE table_name = UPPER('你的表名');
| 情況 | 建議 |
|---|---|
| 頻繁出現在 WHERE/JOIN/ORDER BY 的欄位 | 建立索引(單欄或複合) |
| 外鍵欄位 | 必須建立索引,避免子表鎖定父表 |
| 高選擇性(唯一值多)欄位 | 適合 B-Tree 索引 |
| 低選擇性(如性別、狀態碼) | 不建立索引,全表掃描更快 |
| 頻繁 UPDATE 的欄位 | 避免納入索引,增加寫入開銷 |
| 大型文字/二進位欄位 | 使用全文索引或檔案流,勿建一般索引 |
ALTER INDEX ... REBUILD / REORGANIZE。sys.dm_db_index_physical_stats 碎片率 > 30% 重建,10-30% 重組。UPDATE STATISTICS 或啟用 AUTO_UPDATE_STATISTICS。