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_

--查询现有所有数据库表的索引情况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 indInner 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_IDInner 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_IDInner Join sys.columns Col on tab.object_ID = Col.object_ID And index_columns.column_ID = Col.column_ID)tbWhere Tab_name not like ‘sys%‘Group By Tab_name,Index_name) indexs Inner Join sys.indexes Ind_Attribute on indexs.Index_name = Ind_Attribute.nameOrder By indexs.Tab_name  --删除所有非主键索引Declare @Tab_name Varchar(200)Declare @Index_name Varchar(200) Declare C_Delindex Cursor Fast_Forward ForSelect indexs.Tab_name,indexs.Index_nameFrom (Select Tab_name,Index_name) indexs Inner Join sys.indexes Ind_Attribute on indexs.Index_name = Ind_Attribute.nameWhere Ind_Attribute.is_primary_key = 0Order By indexs.Tab_name Open C_Delindex Fetch Next From C_Delindex Into @Tab_name,@Index_nameWhile @@Fetch_Status = 0    Begin        Exec(‘DROP INDEX ‘ + @Index_name + ‘ ON ‘ + @Tab_name)        Fetch Next From C_Delindex Into @Tab_name,@Index_name    EndClose C_DelindexDeallocate C_Delindex

总结

以上是内存溢出为你收集整理的SQLServer查询当前数据库所有索引，并使用游标删除相关索引全部内容，希望文章能够帮你解决SQLServer查询当前数据库所有索引，并使用游标删除相关索引所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。