전체 테이블의 모든 인덱스 조회

Declare @TblName sysname, @ObjId int, @IndexName sysname, @IndexId int, @GroupId int

, @ignore_dup_key bit, @is_unique bit, @is_hypothetical bit, @is_primary_key bit, @is_unique_key bit

, @keys nvarchar(2126), @groupname sysname

declare TblObjects cursor local static for

Select objects.object_Id, objects.Name, Indexes.Name, index_id, data_space_id

, ignore_dup_key, is_unique, is_hypothetical, is_primary_key, is_unique_constraint

From sys.objects As objects

Inner Join sys.indexes As Indexes On objects.object_Id=Indexes.object_Id

Where objects.Type='U'

open TblObjects

fetch TblObjects into @ObjId, @TblName, @IndexName, @IndexId, @GroupId, 

@ignore_dup_key, @is_unique, @is_hypothetical, @is_primary_key, @is_unique_key

Create Table #Index

(

tblName sysname

, indexName sysname null

, IndexId int

, ignore_dup_key bit

, is_unique bit

, is_hypothetical bit

, is_primary_key bit

, is_unique_key bit

, groupname sysname collate database_default NULL

, indexKeys nvarchar(2126)

)

while @@fetch_status >= 0

begin

declare @i int, @thiskey nvarchar(131) 

select @keys = index_col(@TblName, @IndexId, 1), @i = 2

if (indexkey_property(@objid, @IndexId, 1, 'isdescending') = 1)

select @keys = @keys  + '(-)'

select @thiskey = index_col(@TblName, @IndexId, @i)

if ((@thiskey is not null) and (indexkey_property(@objid, @IndexId, @i, 'isdescending') = 1))

select @thiskey = @thiskey + '(-)'

while (@thiskey is not null )

begin

select @keys = @keys + ', ' + @thiskey, @i = @i + 1

select @thiskey = index_col(@TblName, @IndexId, @i)

if ((@thiskey is not null) and (indexkey_property(@objid, @IndexId, @i, 'isdescending') = 1))

select @thiskey = @thiskey + '(-)'

end

select @groupname = null

select @groupname = name from sys.data_spaces where data_space_id = @groupid

-- INSERT ROW FOR INDEX

insert into #Index values (@TblName, @IndexName, @IndexId

, @ignore_dup_key, @is_unique, @is_hypothetical, @is_primary_key, @is_unique_key

, @groupname, @keys)

-- Next index

fetch TblObjects into @ObjId, @TblName, @IndexName, @IndexId, @GroupId, 

@ignore_dup_key, @is_unique, @is_hypothetical, @is_primary_key, @is_unique_key

end

deallocate TblObjects

select

tblName,

indexName,

indexKeys,

PK=case when is_primary_key <>0 then 'PK' else 'IX' end,

UniqueYN=case when is_unique <>0 then 'Y' else 'N' end,

'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group

case when IndexId = 1 then 'clustered' else 'nonclustered' end

+ case when ignore_dup_key <>0 then ', ignore duplicate keys' else '' end

+ case when is_unique <>0 then ', unique' else '' end

+ case when is_hypothetical <>0 then ', hypothetical' else '' end

+ case when is_primary_key <>0 then ', primary key' else '' end

+ case when is_unique_key <>0 then ', unique key' else '' end

+ ' located on ' + groupname)

from #Index

order by tblName, IndexId, indexName

Drop Table #Index