DB상의 모든 인덱스 정보를 보이는 저장프로시저

은근히 DB의 인덱스를 전체적으로 체크해야 할 일이 근근히 있다.

그 때 마다 정보를 수집해서 체크하는 것이 만만치 않앗는데 ㅍ.ㅍ

보통은 SqlCompare 를 이용하거나 Generate를 해서 text 비교 및 검색을 이용해서 처리를 했는데 쿼리로 처리가 가능하네.

출처: DB상의 모든 인덱스 정보를 보이는 저장프로시저

--DB상의모든인덱스정보를보이는저장프로시저

--

--출처: http://www.swynk.com

--

--내용: DB상의모든색인에대해색인의유형, 색인이사용되는컬럼을보여줍니다.

--

--개인적으로현재참유용하게사용하고있는저장프로시져입니다.

--

--많은도움되시길바랍니다. 그럼이만.

create procedure psp_help_allidx7

as

/* Purpose: to list all indexes for each table

   Author : Eddy Djaja, Publix Super Markets, Inc.

   Revision: 12/07/1999 born date

*/

   

-- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY

declare @empty varchar(1)

select @empty = ''

declare @des1       varchar(35), -- 35 matches spt_values

       @des2        varchar(35),

       @des4        varchar(35),

       @des32       varchar(35),

       @des64       varchar(35),

       @des2048     varchar(35),

       @des4096     varchar(35),

       @des8388608  varchar(35),

       @des16777216 varchar(35)

select @des1 = name from master.dbo.spt_values where type = 'I' andnumber = 1

select @des2 = name from master.dbo.spt_values where type = 'I' andnumber = 2

select @des4 = name from master.dbo.spt_values where type = 'I' andnumber = 4

select @des32 = name from master.dbo.spt_values where type = 'I' andnumber = 32

select @des64 = name from master.dbo.spt_values where type = 'I' andnumber = 64

select @des2048 = name from master.dbo.spt_values where type = 'I'and number = 2048

select @des4096 = name from master.dbo.spt_values where type = 'I'and number = 4096

select @des8388608 = name from master.dbo.spt_values where type ='I' and number = 8388608

select @des16777216 = name from master.dbo.spt_values where type ='I' and number = 16777216

select        o.name,

       i.name,

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

                           case when (i.status & 16)<>0 then 'clustered' else 'nonclustered' end

                           + case when (i.status & 1)<>0 then ', '+@des1 else @empty end

                           + case when (i.status & 2)<>0 then ', '+@des2 else @empty end

                           + case when (i.status & 4)<>0 then ', '+@des4 else @empty end

                           + case when (i.status & 64)<>0 then ', '+@des64 else

                                        case when (i.status & 32)<>0 then ', '+@des32 else @empty end end

                           + case when (i.status & 2048)<>0 then ', '+@des2048 else @empty end

                           + case when (i.status & 4096)<>0 then ', '+@des4096 else @empty end

                           + case when (i.status & 8388608)<>0 then ', '+@des8388608 else @empty end

                           + case when (i.status & 16777216)<>0 then ', '+@des16777216 else @empty end),

       'index column 1' = index_col(o.name,indid, 1),

       'index column 2' = index_col(o.name,indid, 2),

       'index column 3' = index_col(o.name,indid, 3)

from sysindexes i, sysobjects o

where i.id = o.id

             and indid > 0

             and indid < 255

             and o.type = 'U'

             --exclude autostatistic index

             and (i.status & 64) = 0

             and (i.status & 8388608) = 0

             and (i.status & 16777216)= 0

             order by o.name

go

--사용방법

exec psp_help_allidx7

go

DB