특정 Table을 사용하는 StoredProcedure 찾기! Go~ Go~

이번에는 간단한 쿼리에 대한 내용을 정리해서 올립니다. 원래 이 내용은 제가 친하게 알고 지내는 사람이 하나 있는데, 어느 날 메신져로 갑자기 이런 내용을 물어보더라구요…

그래서 알려준 내용인데, 좀 지나서 그 친구 말을 들어보니, 그 친구네 팀원들 중 이 내용에 대해서 알고 있는 사람이 별로 없다고 이야기를 하더라구요~ 그래서 한번 올려봅니다. 한 분 이라도 도움이 될 지 모른다는 생각에.. ^^v

특정 Table을 사용하는 StoredProcedure 찾기!

이번에는 MS-SQL에서 사용하는 시스템 테이블에 대해서 간략히 알아보도록 하겠다. 실제로 실무에서 프로젝트를 진행할 때 종종 유용하게 사용되는 테이블이 바로 시스템 테이블인데(사실 개발단계에서는 개발자가 이 테이블을 건드릴 일은 거의 없을 것이다. 주로 공통팀이나, DBA 정도가 사용 할 수 있겠다.) 이 시스템 테이블은 데이터베이스에서 생성되는 모든 개체를 관리하기 위해 혹은 수많은 내부작업에 필요한 메타데이터를 저장하기 위한 용도로 주로 사용이 된다.

그럼 본문에서 이야기 할 내용을 살짝 이야기하자면, 특정 테이블을 사용하고 있는 모든 프로시져를 찾기 위한 쿼리를 알아 볼 것이다.

실무에서 자주 사용된다는 썰은 도저히 못하겠지만, 그래도 재미있지 않을까? 라는 생각으로 정말 용감하게 올린다. ^^; (사실은 3번 고민했음~~)

너무 기술적인 부분만 읽다 보면, 너무 지치고 지겹지 않은가~ 이런 나름 재미있을 법한 것으로 살짝 환기를 시켜주기 위한다고 살짝 둘러대고, 이제 한 번 시작해 보자.

SysDepends

먼저 이야기해야 할 것은 여기에 사용되는 시스템 테이블이다. 그 중에 하나는 SysDepends라는 테이블인데, 이 테이블이 뭐 하는 놈인지에 대해서 SQL Help에 정의되어 있는 말을 인용하자면 “개체(뷰, 프로시저 및 트리거)와 그 정의에 포함된 개체(테이블, 뷰 및 프로시저) 간의 종속 관계에 관한 정보를 포함합니다. 이 테이블은 각 데이터베이스에 저장됩니다.”라고 되어있다. 일단 테이블 이름만 봐도 상당히 의존적일 것 같은 feel이 마구 풍기는데, 말 그대로 종속관계에 대한 정보를 포함하고 있는 테이블이다.

열 이름	데이터 형식	설명
id	int	개체 ID입니다.
depid	int	종속 개체의 ID입니다.
number	smallint	프로시저 번호입니다.
depnumber	smallint	종속 프로시저의 번호입니다.
status	smallint	내부 상태 정보입니다.
depdbid	smallint	예약되어 있습니다.
depsiteid	smallint	예약되어 있습니다.
selall	bit	개체가 SELECT * 문에서 사용되는 경우에 설정됩니다.
resultobj	bit	개체가 업데이트되는 경우에 설정됩니다.
readobj	bit	개체가 판독되는 경우에 설정됩니다.

위는 SysDepends 테이블의 컬럼 정보다. 이해하기 어렵지 않게 정의되어 있다. 이중에 우리가 사용 할 컬럼은 id와 depid이다. Id 컬럼은 개체의 id값이고, depid는 종속된 개체의 id이다. 이 id값에 대한 정보는 아래에 간략히 이야기 할 Sysobjects라는 테이블의 id값과 참조 관계가 있다.

SysObjects

이 테이블은 데이터베이스 내에 존재하는 모든 개체에 대한 기본적인 정보를 저장하고 있는 테이블이다. 여기서 개체라 함은 모두 알다시피 테이블, 뷰, 프로시져, 함수, 트리거 등이 되겠다. 이러한 개체는 모두 SysObjects 테이블에 유일한 key를 가지고 있는데, 이것이 바로 id값이고, 이 id값으로 Foreign key 관계가 설정된 테이블들과 조인을 통해 데이터를 조회 할 수 있다.

그리고 여기서 한가지 더 확인해야 할 것은 xtype 컬럼인데 이 xtype 컬럼은 저장된 개체의 종류가 무엇인지를 구분하는 구분 값 되겠다. 이 구분 값이 정의하고 있는 것이 무엇인지 확인해 보자.

C = CHECK 제약 조건 D = 기본값 또는 DEFAULT 제약 조건 F = FOREIGN KEY 제약 조건 L = 로그 FN = 스칼라 함수 IF = 인라인 테이블 함수 P = 저장 프로시저 PK = PRIMARY KEY 제약 조건(유형은 K) RF = 복제 필터 저장 프로시저 S = 시스템 테이블 TF = 테이블 함수 TR = 트리거 U = 사용자 테이블 UQ = UNIQUE 제약 조건(유형은 K) V = 뷰 X = 확장 저장 프로시저

이런 구분 값을 통해서 각각 개체의 타입을 확인 할 수 있다.

여기까지 확인했으니, 이제 다 끝났다. 이제 실제 쿼리를 작성하기만 하면 된다.

SELECT DISTINCT O.NAME FROM SYSDEPENDS AS D INNER JOIN SYSOBJECTS AS O ON D.ID = O.ID WHERE D.DEPID = [테이블ID] AND O.XTYPE = 'P'

위 쿼리는 볼 것도 없이 두 테이블을 조인해서 필요한 프로시져명을 출력하는 간단한 쿼리이다. Where 조건 중에 D.DEPID는 종속된 테이블 ID값이고, 이 테이블 ID값은 역시 SysObjects 테이블에서 확인 할 수 있다.

(SELECT ID FROM SYSOBJECTS WHERE NAME = “특정 테이블명”)

그리고 O.XTYPE에서 할당한 ‘P’는 위에서 설명한 프로지셔를 뜻한다.

이렇게 해서, 본문에서 설명하고자 한 초 간단 허접 쿼리를 완성했다. ^^;

실제 본문에서 살짝 설명한 SysObjects 테이블은 많은 컬럼을 가지고 있으니, 필요하다면 한번 슬쩍 훑어보는 것도 나름 도움이 될 것이다.


*Updaet 2008-06-11
- 위와 유사하게 특정 컬럼을 사용하는 프로시져 및 함수를 찾는 쿼리를 살짝 추가한다.
예를 들어 trace_yn 이라는 컬럼을 사용하는 프로시져 및 함수를 찾고자 할 때 다음과 같은 쿼리를
사용할 수 있다.

SELECT CASE O.XTYPE
WHEN 'P' THEN '저장 프로시져'
WHEN 'FN' THEN '함수'
END AS TYPE,
O.NAME
FROM SYSDEPENDS AS D
INNER JOIN SYSOBJECTS AS O ON D.ID = O.ID
WHERE D.DEPID IN
(SELECT O.ID
FROM SYSOBJECTS AS O
INNER JOIN SYSCOLUMNS AS C ON O.ID = C.ID
WHERE C.NAME = 'trace_yn' )
AND O.XTYPE IN ('P' , 'FN')
GROUP BY O.NAME, O.XTYPE