[DB] 스토어드 프로시져(SP) 최종 수정정보 확인방법

데이터베이스를 사용하면서 보안이나 속도상 이슈로 인해 스토어드 프로시져(SP)를 많이 사용합니다.

SP를 수정하다보면 마지막 수정이력을 확인할 필요가 있습니다.

그럴 때 사용할 수 있는 쿼리입니다.

 

 

< SP수정정보 확인방법 >

 

목차(Contents)

 

사용방법

 

여러 명이 SP수정하고 사용하다 보면 최종이력 확인이 필요합니다.

아래의 쿼리를 사용해도 수정이력 전체를 확인할 수는 없습니다.

수정이력을 남기기 위해서는 별도의 로그로 관리해야 합니다.

 

그래도 최종수정시간 및 내역을 조회 할 수 있어 최소한의 기록은 확인할 수 있습니다. 

시스템 정보 조회 시에 많이 사용하는 INFORMATION_SCHEMA.ROUTINES를 이용해서 sp수정시간을 확인할 수 있는 쿼리입니다.

 

SELECT SPECIFIC_CATALOG               -- 카탈로그의 명칭(SP가 생성된 데이터베이스 명칭)
       , SPECIFIC_SCHEMA              -- 스키마 명칭
       , SPECIFIC_NAME                -- 카탈로그의 명칭(스토어드 프로시저 이름)
       , ROUTINE_SCHEMA               -- 카탈로그의 명칭(스토어드 프로시저 이름)
       , ROUTINE_NAME                 -- 카탈로그의 명칭(스토어드 프로시저 이름)
       , ROUTINE_TYPE                 -- 스토어드 프로시저인 경우 'PROCEDURE', 함수의 경우 'FUNCTION'
       , ROUTINE_DEFINITION           -- 스토어드 프로시저 및 함수의 내용 텍스트에서 앞 4000자만 출력됨  
       , CREATED                      -- SP를 최초로 생성한 날짜
       , LAST_ALTERED                 -- 마지막으로 SP를 수정한 날짜
  FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE = 'PROCEDURE'     -- PROCEDURE or FUNCTION으로 사용
   AND SPECIFIC_NAME = '스토어드프로시저 이름'
 ORDER BY LAST_ALTERED DESC

 

위의 쿼리가 복사 안 될 경우 아래의 텍스트를 복사하세요.

SELECT SPECIFIC_CATALOG               -- 카탈로그의 명칭(SP가 생성된 데이터베이스 명칭)
               , SPECIFIC_SCHEMA              -- 스키마 명칭
               , SPECIFIC_NAME                   -- 카탈로그의 명칭(스토어드 프로시저 이름)
               , ROUTINE_SCHEMA               -- 카탈로그의 명칭(스토어드 프로시저 이름)
               , ROUTINE_NAME                    -- 카탈로그의 명칭(스토어드 프로시저 이름)
               , ROUTINE_TYPE                     -- 스토어드 프로시저인 경우 'PROCEDURE', 함수의 경우 'FUNCTION'
               , ROUTINE_DEFINITION         -- 스토어드 프로시저 및 함수의 내용 텍스트에서 앞 4000자만 출력됨  
               , CREATED                                -- SP를 최초로 생성한 날짜
               , LAST_ALTERED                     -- 마지막으로 SP를 수정한 날짜
    FROM INFORMATION_SCHEMA.ROUTINES

 WHERE ROUTINE_TYPE = 'PROCEDURE'                     -- PROCEDURE or FUNCTION으로 사용
       AND SPECIFIC_NAME = '스토어드프로시저 이름'
 ORDER BY LAST_ALTERED DESC

 

위의 SP를 실행하고 나면 아래의 이미지처럼 결과가 표시됩니다.

많은 정보는 아니지만 SP의 변경정보는 오류 수정에 도움이 될 수 있습니다.

 

< 수정정보 조회결과 >

 

위의 쿼리에서 ROUTINE_DEFINITION는 스토어드 프로시저의 내용 중 일부(앞쪽 4000자 정도)만 나오게 됩니다.

전체내용을 보기 위해서는 OBJECT_DEFINITION 함수를 이용하거나 sys.sql_modules 사용하여야 합니다.

 

OBJECT_DEFINITION 함수 및 sys.sql_modules에 대해서는 다음에 다시 다뤄보도록 하겠습니다.