키워드로 PROCEDURE, FUNCTION 찾기

키워드로 PROCEDURE, FUNCTION 찾기

 

• Version : SQL Server 2000, 2005, 2008, 2008R2, 2012

 

개발 과정에서 많이 받는 질문 중 하나가 특정 테이블(키워드)을 사용하는 프로시저를 모두 찾을 수 있냐는 것이다. 당연히 프로시저 안에서 테이블을 호출 하는 코드가 있다면 검색이 가능하다.

 

시스템 테이블과 INFORMATION_SCHEMA.ROUTINE 방법을 이용하여 검색하는 방법을 알아 보자.

 

[INFORMATION_SCHEMA.ROUTINE 사용]

INFORMATION_SCHEMA.ROUTINE 을 사용한 방법이다.

• ROUTINE_DEFINITION : 사용자가 작성한 쿼리문
• ROUTINE_TYPE에 : 프로시저 또는 함수 인지를 구분

 

프로시저 검색.

SELECT     ROUTINE_NAME, ROUTINE_SCHEMA, CREATED, LAST_ALTERED, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%PERSON%'     AND ROUTINE_TYPE = 'PROCEDURE' ORDER BY ROUTINE_NAME

 

 

 

함수 검색

SELECT     ROUTINE_NAME, ROUTINE_SCHEMA, CREATED, LAST_ALTERED, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%PERSON%'     AND ROUTINE_TYPE = 'FUNCTION' ORDER BY ROUTINE_NAME

 

 

 

[시스템 테이블 사용]

시스템 테이블에는 sys.sysobjects 와 sys.syscomments 테이블이 사용 된다. Sys.sysobjects 테이블은 데이터베이스의 모든 오브젝트 (테이블, 프로시저, 함수, FK 등)의 정보가 있으며 sys.syscomments 테이블에는 해당 오브젝트의 정의가 들어 있다. 두 테이블은 id로 조인 하면 된다.

SELECT     A.NAME, A.CRDATE, A.REFDATE, B.TEXT FROM SYS.SYSOBJECTS AS A     INNER JOIN SYS.SYSCOMMENTS AS B     ON A.ID = B.ID WHERE B.TEXT LIKE '%PERSON%' AND A.TYPE = 'P' ORDER BY A.NAME

 

 

 

스키마를 자주 변경하는 것은 좋지 못한 환경이지만 부득이하게 스키마를 변경해야 할 때 관련된 프로시저 및 함수를 찾을 때 매우 유용하게 사용 할 수 있을 듯 하다.

 

2013-01-09 / 강성욱 / http://sqlmvp.kr