MSSQL 데이터베이스를 다루다가 프로시저를 수정해야 할 때가 종종 있다.
특정 기능이 어떤 프로시저에 구현되어 있는지,
또는 오류 수정을 위해 특정 테이블을 사용하는 프로시저들을 찾아야 하는 경우가 생긴다.
수 많은 저장 프로시저 중에서 특정 문자열이 포함된 녀석들을 찾아야 할 때,
마침 어떤 프로시저에 있는지 기억이 나지 않는다.
이런 상황에서 내가 찾는 문자열이 포함된 프로시저를
일일이 하나씩 들춰봐야하는 답답한 상황에 직면하게 된다.
이번 글에서는 프로시저 중에서의 특정 내용 확인을 쉽게 확인 할 수 있도록
MS-SQL에서 저장 프로시저 내에 포함된 내용을 찾아보는 방법을 알아보자.
저장 프로시저 특정 글자 찾기 SQL 쿼리
구문
바쁜 사람들의 시간 절약을 위해 일단 쿼리 구문부터 봐보자.
LIKE
뒤에 검색할 내용을 입력하면 된다.
SELECT
p.name AS ProcedureName
FROM
sys.procedures p
INNER JOIN
sys.sql_modules m ON p.object_id = m.object_id
WHERE
m.definition LIKE '%검색내용%';쿼리에 사용된 시스템 테이블에 대해 더 자세히 알아보기
프로시저 내의 문자열을 찾을 때 사용된 테이블은 시스템 테이블이다.
✅ 시스템 테이블이란?
데이터베이스 시스템의 관리 및 운영에 필요한 정보를 담고 있는 특별한 테이블이다.
이 테이블은 사용자가 직접 생성하는 테이블과는 달리, MSSQL 서버에 의해 자동으로 생성되고 관리된다.
그 중 본문에서 사용된 프로시저의 포함된 내용을 찾기 위해 사용된 시스템 테이블은
프로시저의 정보를 담고 있는
sys.procedures
,
sys.sql_modules
를 사용하였다.
각 시스템 테이블의 역할부터 알아보자.
✅ sys.procedures
sys.procedures 테이블은 현재 데이터베이스에 정의된 모든 저장 프로시저에 대한 정보를 담고 있다.
- name: 저장 프로시저의 이름
- object_id: 저장 프로시저의 고유 ID
- create_date: 저장 프로시저가 생성된 날짜
- modify_date: 저장 프로시저가 마지막으로 수정된 날짜
예시: 프로시저 이름이 ‘test’인 프로시저가 있는지 찾아보기
SELECT *
FROM sys.procedures
WHERE name = 'test';✅ sys.sql_modules
sys.sql_modules 테이블은 저장 프로시저를 포함한 모든 데이터베이스 객체의 정의를 담고 있다.
- object_id: 객체의 고유 ID
- definition: 객체의 정의, 즉 SQL 코드를 의미한다.
예시: 프로시저 이름이 ‘test’인 프로시저의 정의 내용 확인하기 (SQL 정의 쿼리 확인)
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.test');사용된 시스템 테이블의 역할을 알고,
프로시저 내 문자열을 검색하는 쿼리를 다시 봐본다면
왜 내가 원하는 문자열이 포함된 프로시저가 조회되는지 알 수 있다.
이해하기 위해서 쓴 글이지만,
사실 나도 ‘복사-붙여넣기’로 사용하기 때문에 외워서 쓰진 않는다 ^^;