키워드로 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 |
스키마를 자주 변경하는 것은 좋지 못한 환경이지만 부득이하게 스키마를 변경해야 할 때 관련된 프로시저 및 함수를 찾을 때 매우 유용하게 사용 할 수 있을 듯 하다.
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
