查询所有索引

--查询所有索引

SELECT
tab.name AS [表名],
idx.name AS [索引名称],
col.name AS [列名]
FROM
sys.indexes idx
JOIN sys.index_columns idxCol 
ON (idx.object_id = idxCol.object_id 
AND idx.index_id = idxCol.index_id 
)
JOIN sys.tables tab
ON (idx.object_id = tab.object_id)
JOIN sys.columns col
ON (idx.object_id = col.object_id
AND idxCol.column_id = col.column_id);

--查询所有包含%index%的索引并删除 （指定字段可避免删除外键索引）

复制查询结果并执行！

SELECT
'drop index ' +
idx.name AS [索引名称],+ 'on '+
tab.name AS [表名]
FROM
sys.indexes idx
JOIN sys.index_columns idxCol 
ON (idx.object_id = idxCol.object_id 
AND idx.index_id = idxCol.index_id 
)
JOIN sys.tables tab
ON (idx.object_id = tab.object_id)
JOIN sys.columns col
ON (idx.object_id = col.object_id
AND idxCol.column_id = col.column_id)
where idx.name like '%index%';