sysindexes 활용

 

1. 데이타가 없는 테이블(데이터행수=0)

select object_name(id) as 테이블명, name, indid, dpages, *

from sysindexes

where id > 100

and rows = 0

and indid <=1

order by 1

 

2. 클러스터 인덱스가 없는 테이블

select object_name(id) as 테이블명, name, indid, dpages, *

from sysindexes

where id > 100

and indid < 2

and id not in(

    select id

    from sysindexes

    where id > 100

    and indid = 1

)

order by 1

 
3. 인덱스가 하나도 없는 테이블

select object_name(id) as 테이블명, name, indid, dpages, *

from sysindexes

where id not in(

    select id

    from sysindexes

    where id > 100

    and indid > 0

)

and id > 100

and indid = 0