[MS-SQL] 기존 프로시져 내에서 텍스트 검색하기

 오늘 얘기 해 볼 내용은

MS-SQL의 프로시져 검색에 대한 이야기이다.

시스템을 운영하기 위해서는

데이터를 다루기 위해 여러가지 방법을 사용할 수 있지만

그 중에서도 스토어드 프로시져를 많이들 사용할 것이다.

다행히도 프로시져를 만들 때 마다 문서를 작성하고

문서의 업데이트가 잘 되어 있는 시스템 운영자라면 이 글은 패스해도 상관없을 듯 하다.

하지만... 시스템 운영 또는 개발을 하다보면

문서의 최신화를 유지하는 문제가 그리 쉬운 일은 아닐 것이라는 것도 현실이다.

따라서 큰 시스템을 운영하는 입장에서는

예전에 만들었던 프로시져를 찾거나

어떤 테이블의 컬럼을 insert, update, delete 등의 작업을 담당하는 프로시져를 찾는 작업은

누군가에겐 어려운 일이 될 수도 있다는 것이다.

이런 상황에서 프로시져 내부의 텍스트를 키워드로 해서

해당 컬럼을 참조하고 있는 프로시져를 찾는 기능은 관리자 입장에서 알아두면 매우 유용할 듯 싶다.

SQLServer

바로 본론으로 들어가서

프로시져 내부의 텍스트를 검색하는 방법은 몇 가지가 있는데

먼저 sysobjects 와 syscomments 뷰를 사용 하는 방법이다.

sysobjects는 SQLServer에서 제공하는 뷰인데 DB에서 만들어진 객체들에 대한 정보를 담고있다.

자세한 내용은 아래 링크를 참조하면 좋다.

https://docs.microsoft.com/ko-kr/sql/relational-databases/system-compatibility-views/sys-sysobjects-transact-sql?view=sql-server-ver15

암튼... sysobjects의 type이란 컬럼이 있는데 다음과 같은 속성값을 가지고 있다.

AF = 집계 함수(CLR) C = CHECK 제약 조건 D = 기본값 또는 DEFAULT 제약 조건 F = FOREIGN KEY 제약 조건 L = 로그 FN = 스칼라 함수 FS = 어셈블리(CLR) 스칼라 함수 FT = 어셈블리(CLR) 테이블 반환 함수 IF = 인라인 테이블 함수 IT = 내부 테이블 P = 저장 프로시저 PC = 어셈블리(CLR) 저장 프로시저 PK = PRIMARY KEY 제약 조건(K 유형) RF = 복제 필터 저장 프로시저 S = 시스템 테이블 SN = 동의어 SQ = 서비스 큐 TA = 어셈블리(CLR) DML 트리거 TF = 테이블 함수 TR = SQL DML 트리거 TT = 테이블 유형 U = 사용자 테이블 UQ = UNIQUE 제약 조건(K 유형) V = 뷰 X = 확장 저장 프로시저

그 중에서 우리가 필요한 건 프로시져니까 type을 'P'로주면 프로시져를 찾을 수 있다.

그리고 syscomments를 사용해서 프로시져 텍스트를 검색할 수 있는데 syscomments 자세한 내용은 아래 링크를 참조.

https://docs.microsoft.com/ko-kr/sql/relational-databases/system-compatibility-views/sys-syscomments-transact-sql?view=sql-server-ver15

암튼, sysobjects와 syscomments뷰의 ID값을 키로 join 하여 프로시져에서 텍스트를 검색할 수 있는데

SQL 쿼리는 다음과 같다.

select *
from sysobjects s
left outer join syscomments t
on s.id = t.id
where s.type = 'p'
and t.text like '%검색할 내용%'

※ 근데... 이 방법은 추천하기가 좀 어려운데... 이유는 MS에서 다음과 같이 공지를 하고 있기 때문에...

sql_modules에 대해서는 기회가 된다면 다음에 알아보고 정리 하는걸로....

중요 Microsoft SQL Server의 이후 버전에서는 이 기능이 제거됩니다. 새 개발 작업에서는 이 기능을 사용하지 말고, 현재 이 기능을 사용하는 애플리케이션은 가능한 한 빨리 수정하세요. 대신 sys.sql_modules를 사용하는 것이 좋습니다. 자세한 내용은 sql_modules (transact-sql)을 참조 하십시오.

그리고 다음 방법은 sys.sysprocedures를 사용하는 방법인데

우선 쿼리를 먼저 보자면 다음과 같다.

select OBJECT_NAME(object_id)
, OBJECT_DEFINITION(object_id)
from sys.procedures
where OBJECT_DEFINITION(object_id) like '%검색할 내용%'

 

sys.procedure는 이름만 봐도 DB내 작성되어 있는 프로시져 객체들에 대한 정보를 담고 있는 뷰이고

object_id를 매개변수로 받는 object_definition이란 기본 제공 함수를 사용해서

프로시져 내부의 텍스트를 검색하는 방식이다.

좀 더 부연설명을 하자면

SQLServer의 모든 객체는 모두 고유의 id값 즉 object_id값을 가지고 있는데

object_definition함수를 사용해서 object_id에 해당하는 객체가 정의된 내용을 찾아 거기서 like검색을 하는 것.

다만, 프로시져가 아주 많을 경우에는 like검색의 특성상 조금 검색이 늦어질 수 도 있다.

(경험상 시스템에 부하가 걸릴정도는 아님....)

암튼...

이 정도의 방법이 있다는 것만 숙지하고 있으면

기존 또는 새로 시스템 운영을 맡게된 시스템 운영자들 모두에게 조금의 도움은 될 수 있을 것 같아서 정리해 보았다.

그럼 끝~