MS Sql 의 시스템 테이블 정보

MSSQL의 시스템 테이블에는 여러가지 정보가  담겨져 있습니다.  예를  들어서 테이블의 카운트를 구하기 위해서는 count() 함수를 이용하는것이 일반적입니다.

 

ex)

--count() 함수를 이용한 테이블의 COUNT정보

select count(*) from TABLE_NAME

go

 

하지만 데이터 베이스의 모든 테이블의 카운트 정보를 알고 싶다면 이렇게 한 테이블씩 카운트를 한다면 무척이나 시간이 걸릴것 입니다. 그러나 시스템 정보 테이블을 이용한다면 한번의 쿼리로 데이터베이스 상의 모든 테이블의 카운트를 구할 수 있습니다.

 

ex)

--시스템 테이블을 이용한 각 테이블의 COUNT정보
select o.name as TABLE_NAME, i.rows as ROW_COUNT_ALL
from sysindexes i, sysobjects o
where o.xtype = 'U'
and o.name <> 'sysdiagrams'
and o.id = i.id
order by o.name
go

 

이렇게 구하면 아무리 많은 양의 레코드를 가진 테이블이라 할 지라도 count()함수 보다 월등히 빠른 속도로 카운트 정보를 가져올 수 있습니다.  설명은 간단히 끝내고 몇가지 유용한 시스템 테이블 정보를 소개 하겠습니다.

 

--특정 저장 프로시져의 내용정보

select o.name,c.text from syscomments c , sysobjects o
where c.id = o.id
and o.name = 'SP_NAME'
order by c.id,c.colid

go

 

--뷰를 작성한 SELECT문장 정보

select o.name,c.text from syscomments c , sysobjects o
where c.id = o.id
and o.name = 'VIEW_NAME
order by c.id,c.colid

go

 

--특정 테이블의 컬럼 정보
select o.name,c.name
from syscolumns c, sysobjects o
where c.id=o.id
and o.name = TABLE_NAME

go

 

--특정 테이블의 인덱스 정보
select o.name,col.name
from sysindexkeys ik, sysobjects o, syscolumns c,
   (select i.id,i.indid,i.status
    from sysindexes i
    where (i.status & 2048)<>0) sub_i
where ik.id=o.id
and ik.id=c.id
and ik.colid=c.colid
and ik.id=sub_i.id
and ik.indid=sub_i.indid
and o.name='TABLE_NAME'
order by ik.id,ik.indid,ik.keyno

go

 

--현재 서버상에  존재하는 프로세서 정보

select
spid,kpid,lastwaittype,status,hostname,program_name
from master..sysprocesses

go

 

--특정일 이후에 작성된 오브젝트 객체만을 알고 싶을때

select o.name table_name, substring(v.name + x.name,1,16) type , o.crdate create_date

,substring(user_name(uid),1,8) owner
from sysobjects o, master.dbo.spt_values v, master.dbo.spt_values x
where o.sysstat & 0xf = v.number
and v.type = 'O'
and x.type = 'R'
and o.userstat & -32768 = x.number
and o.crdate >= 'DATE_BASE'

order by 4,2,3,1

go

 

--현재 트랜잭션 락 현상을 일으키는 세션정보와 대상 오브젝트 정보

select  convert (smallint, req_spid) spid,
rsc_dbid dbid,rsc_objid objId,o.name objname,rsc_indid IndId,
substring (v.name, 1, 4) type,substring (rsc_text, 1, 16) resource,
substring (u.name, 1, 8) mode,substring (x.name, 1, 5) status
from master.dbo.syslockinfo,master.dbo.sysobjects o,
master.dbo.spt_values v,master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and master.dbo.syslockinfo.rsc_objid *= o.id and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number and u.type = 'L'
order by spid

go

 

--테이블의 인덱스를 강제로 타게 만들기

select * from TABLE_NAME tablename WITH(INDEX(PK_NAME))
where tablename.empno = 1
go

 

이상으로 시스템 정보테이블의 설명을 마치겠습니다. 이 밖에도 시스템 정보 테이블로 알 수 있는 많은 정보가 있습니다.  이밖에 정보나 좋은 자료 있으시면 저에게도 알려주시면 감사..^^

 

대한민국 개발자 여러분 홧팅하세요.