SQLServer查询当前数据库所有索引，并使用游标删除相关索引

--查询现有所有数据库表的索引情况
Select indexs.Tab_Name As [表名],indexs.Index_Name As [索引名],indexs.[Co_Names] As [索引列],Ind_Attribute.is_primary_key As [是否主键],Ind_Attribute.is_unique As [是否唯一键],Ind_Attribute.is_disabled As [是否禁用]
From (
Select Tab_Name,Index_Name,[Co_Names]=stuff((Select ‘，‘+[Co_Name] From
( Select tab.Name As Tab_Name,ind.Name As Index_Name,Col.Name As Co_Name From sys.indexes ind
Inner Join sys.tables tab on ind.Object_id = tab.object_id And ind.type in (1,2)
Inner Join sys.index_columns index_columns on tab.object_id = index_columns.object_id And ind.index_id = index_columns.index_id
Inner Join sys.columns Col on tab.object_id = Col.object_id And index_columns.column_id = Col.column_id
) t Where Tab_Name=tb.Tab_Name And Index_Name=tb.Index_Name for xml path(‘‘)),1,‘‘)
From (
Select tab.Name As Tab_Name,2)
Inner Join sys.index_columns index_columns on tab.object_id = index_columns.object_id And ind.index_id = index_columns.index_id
Inner Join sys.columns Col on tab.object_id = Col.object_id And index_columns.column_id = Col.column_id
)tb
Where Tab_Name not like ‘sys%‘
Group By Tab_Name,Index_Name
) indexs Inner Join sys.indexes Ind_Attribute on indexs.Index_Name = Ind_Attribute.name
Order By indexs.Tab_Name
 
 
--删除所有非主键索引
Declare @Tab_Name Varchar(200)
Declare @Index_Name Varchar(200)
 
Declare C_DelIndex Cursor Fast_Forward For
Select indexs.Tab_Name,indexs.Index_Name
From (
Select Tab_Name,Index_Name
) indexs Inner Join sys.indexes Ind_Attribute on indexs.Index_Name = Ind_Attribute.name
Where Ind_Attribute.is_primary_key = 0
Order By indexs.Tab_Name
 
Open C_DelIndex
 
Fetch Next From C_DelIndex Into @Tab_Name,@Index_Name
While @@Fetch_Status = 0
    Begin
        Exec(‘DROP INDEX ‘ + @Index_Name + ‘ ON ‘ + @Tab_Name)
        Fetch Next From C_DelIndex Into @Tab_Name,@Index_Name
    End
Close C_DelIndex
Deallocate C_DelIndex

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!