MSSQL2005 查找索引，和删除表的所有索引

最新推荐文章于 2019-09-19 09:53:54 发布

aqimide

最新推荐文章于 2019-09-19 09:53:54 发布

阅读量1.7k

点赞数

文章标签： object constraints join sql table insert

本文链接：https://blog.csdn.net/aqimide/article/details/6021479

版权

----------查找索引

SELECT
    TableId=O.[object_id],
    TableName=O.Name,
    IndexId=ISNULL(KC.[object_id],IDX.index_id),
    IndexName=IDX.Name,
    IndexType=ISNULL(KC.type_desc,'Index'),
    Index_Column_id=IDXC.index_column_id,
    ColumnID=C.Column_id,
    ColumnName=C.Name,
    Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
        WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
    PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,
    [UQIQUE]=CASE WHEN IDX.is_unique=1 THEN N'√'ELSE N'' END,
    Ignore_dup_key=CASE WHEN IDX.ignore_dup_key=1 THEN N'√'ELSE N'' END,
    Disabled=CASE WHEN IDX.is_disabled=1 THEN N'√'ELSE N'' END,
    Fill_factor=IDX.fill_factor,
    Padded=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
    LEFT JOIN sys.key_constraints KC
        ON IDX.[object_id]=KC.[parent_object_id]
            AND IDX.index_id=KC.unique_index_id
    INNER JOIN sys.objects O
        ON O.[object_id]=IDX.[object_id]
    INNER JOIN sys.columns C
        ON O.[object_id]=C.[object_id]
            AND O.type='U'
            AND O.is_ms_shipped=0
            AND IDXC.Column_id=C.Column_id

-----------删除表的所有索引

declare @tbName varchar(20)
set @tbName='ARAP_Detail'
exec sp_dropindex @tbName

if exists(select 1 from sysobjects where id=object_id('sp_dropindex') and xtype='P')
drop procedure sp_dropindex
go
create procedure sp_dropindex
@tbName varchar(20)=null --索引名
as

if @tbName is null
begin
raiserror('必须提供@tbName参数',12,1)
return
end

create table #
(
   id int identity,
   index_name varchar(50),
   index_description varchar(1000),
   index_keys varchar(100)
)
insert #(index_name,index_description,index_keys)
exec sp_helpindex @tbName

declare @i int,@sql varchar(100)
set @i=1

while @i<=(select max(id) from #)
begin
      if exists(select 1
                from sysobjects A
                join # B on A.name=B.index_name
                where B.id=@i and A.xtype in ('PK','UQ'))
      begin
           select @sql='alter table '+@tbName+' drop constraint '
                       +(select index_name from # where id=@i)
           exec(@sql)
      end
      else
      begin
           select @sql='drop index '+@tbName+'.'
                       +(select index_name from # where id=@i)
           exec(@sql)
      end

      set @i=@i+1
end