--查询现有所有数据库表的索引情况

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, 1, '')

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

)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, [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, 1, '')

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

)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

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

--查询现有所有数据库表的统计情况

Select statss.Tab_Name As [表名],statss.Stat_Name As [统计名] ,statss.[Co_Names] As [统计列],

Sta_Attribute.auto_created As [自动创建],Sta_Attribute.user_created As [用户创建]

From (

Select Tab_Name,Stat_Name, [Co_Names]=stuff((Select '，'+[Co_Name] From

(

Select tab.Name As Tab_Name,sta.Name As Stat_Name,Col.Name As Co_Name From sys.stats sta

Inner Join sys.tables tab on sta.Object_id = tab.object_id

Inner Join sys.stats_columns stat_columns on tab.object_id = stat_columns.object_id And sta.stats_id = stat_columns.stats_id

Inner Join sys.columns Col on tab.object_id = Col.object_id And stat_columns.column_id = Col.column_id

) t Where Tab_Name=tb.Tab_Name And Stat_Name=tb.Stat_Name for xml path('')), 1, 1, '')

From (

Select tab.Name As Tab_Name,sta.Name As Stat_Name,Col.Name As Co_Name From sys.stats sta

Inner Join sys.tables tab on sta.Object_id = tab.object_id

Inner Join sys.stats_columns stat_columns on tab.object_id = stat_columns.object_id And sta.stats_id = stat_columns.stats_id

Inner Join sys.columns Col on tab.object_id = Col.object_id And stat_columns.column_id = Col.column_id

)tb

Where Tab_Name not like 'sys%'

Group By Tab_Name,Stat_Name

) statss Inner Join sys.stats Sta_Attribute on statss.Stat_Name = Sta_Attribute.name

Order By statss.Tab_Name

--删除所有用户创建统计

Declare @Tab_Name Varchar(200)

Declare @Stat_Name Varchar(200)

Declare C_DelStat Cursor Fast_Forward For

Select statss.Tab_Name ,statss.Stat_Name

From (

Select Tab_Name,Stat_Name, [Co_Names]=stuff((Select '，'+[Co_Name] From

(

Select tab.Name As Tab_Name,sta.Name As Stat_Name,Col.Name As Co_Name From sys.stats sta

Inner Join sys.tables tab on sta.Object_id = tab.object_id

Inner Join sys.stats_columns stat_columns on tab.object_id = stat_columns.object_id And sta.stats_id = stat_columns.stats_id

Inner Join sys.columns Col on tab.object_id = Col.object_id And stat_columns.column_id = Col.column_id

) t Where Tab_Name=tb.Tab_Name And Stat_Name=tb.Stat_Name for xml path('')), 1, 1, '')

From (

Select tab.Name As Tab_Name,sta.Name As Stat_Name,Col.Name As Co_Name From sys.stats sta

Inner Join sys.tables tab on sta.Object_id = tab.object_id

Inner Join sys.stats_columns stat_columns on tab.object_id = stat_columns.object_id And sta.stats_id = stat_columns.stats_id

Inner Join sys.columns Col on tab.object_id = Col.object_id And stat_columns.column_id = Col.column_id

)tb

Where Tab_Name not like 'sys%'

Group By Tab_Name,Stat_Name

) statss Inner Join sys.stats Sta_Attribute on statss.Stat_Name = Sta_Attribute.name

Where Sta_Attribute.user_created = 1

Order By statss.Tab_Name

Open C_DelStat

Fetch Next From C_DelStat Into @Tab_Name , @Stat_Name

While @@Fetch_Status = 0

    Begin

        Exec('DROP STATISTICS ' + @Tab_Name + '.' + @Stat_Name)

        Fetch Next From C_DelStat Into @Tab_Name , @Stat_Name

    End

Close C_DelStat

Deallocate C_DelStat