MSSQL 프로시저 내용검색, 문자열 검색 방법

MSSQL은 생성된 프로시저의 내용 및 문자열을 database에 저장하고 있으므로 Query 또는 명령어로 프로시저 내용검색, 문자열을 검색할 수 있습니다.

프로시저 문자열 검색, 내용검색 설명

• 프로시저 명을 알고 있다면 sp_helptext 명령어로 내용검색이 가능합니다. 가장 자주 사용하는 명령어입니다.
• object_id 알고 있다면 OBJECT_DEFINITION을 이용하여 문자열 내용을 검색할 수 있습니다.
• 시스템 뷰에서 직접 검색할 수도 있는데 보통은 syscomments의 TEXT COLUMN을 이용하면 가능합니다.
• 시스템 뷰에서 지원하는 sys.sql_modules는 SQL 언어로 정의된 모든 module을 제공하므로 프로시저 내용도 검색할
• 수 있습니다. 물론 트리거, function 등등 지원합니다.

프로시저 내용검색 흐름도.

프로시저 문자열 검색, 내용검색 사용법

sp_helptext 명령어 사용법.

• 한 개의 프로시저 내용검색을 위해서 sp_helptext 명령어를 사용합니다.

DROP TABLE IF EXISTS UserInfoForContextSearch;
CREATE TABLE UserInfoForContextSearch (
    UserId int,
    UserNm varchar(255)   
);

INSERT INTO dbo.UserInfoForContextSearch(UserId,UserNm) 
VALUES (272, N'Ken')
		,(273, N'Brian')
		,(274, N'Stephen')
		,(275, N'Michael')
		,(276, N'Linda');

 GO
-- DROP PROCEDURE STORE_PROCEDURE_CONTENTS_SEARCH;
CREATE  PROCEDURE [dbo].[STORE_PROCEDURE_CONTENTS_SEARCH]   
    @UserNm NVARCHAR(10) 
AS
BEGIN
   /* 프로시저 내용 검색 */
    SELECT * 
	FROM UserInfoForContextSearch 
	WHERE UserNm = @UserNm	  
	;
END;

--  sp_helptext 명령어.
EXEC sp_helptext 'STORE_PROCEDURE_CONTENTS_SEARCH';

OBJECT_DEFINITION 사용법.

• SELECT 절에서 프로시저의 OBJECT_ID 알고 있다면 프로시저 내용검색이 가능합니다.

-- OBJECT_DEFINITION 사용법.
--example 1
SELECT name AS procedure_name    	 
	 ,OBJECT_DEFINITION(object_id)
  FROM sys.procedures 
 WHERE NAME  ='STORE_PROCEDURE_CONTENTS_SEARCH'

--example 2
 SELECT  OBJECT_NAME(object_id)
        ,OBJECT_DEFINITION(object_id)
   FROM sys.objects
  WHERE  OBJECT_NAME(object_id) = 'STORE_PROCEDURE_CONTENTS_SEARCH'

sys.sql_modules을 사용 프로시저 내용검색.

• 프로시저, 트리거, 함수등 모든 내용검색을 할 때 주로 사용합니다.

-- sys.sql_modules을 사용하여 프로시저 내용 검색.
SELECT OBJECT_NAME(OBJECT_ID) AS  PROC_NM
      ,definition             AS  PROC_SORCE
      ,*
FROM sys.sql_modules  
WHERE object_id = (OBJECT_ID(N'dbo.USP_ProcedureSample'));

syscomments의 TEXT 칼럼 사용.

• syscomments는 시스템 View이며, text 칼럼을 통해서 프로시저 내용을 검색할 수 있습니다.

-- syscomments의  TEXT 컬럼으로 프로시저 문자열 검색.
SELECT type, name, text
  FROM syscomments S1
  JOIN sys.objects S2 
   ON S1.id = S2.object_id
 WHERE S2.type = 'P'
   AND S1.text LIKE '%ContextSearch%';

프로시저 문자열 검색, 내용검색 마무리하며

트리거, function, view 내용검색 방법.

• 필요한 경우 Type 조건을 넣어서 내용검색을 합니다.

 SELECT  TYPE  AS 'OBJECT TYPE' 
        ,TYPE_DESC  AS 'COMMENT'
        ,OBJECT_NAME(object_id) AS 'NAME'
        ,OBJECT_DEFINITION(object_id) AS 'DEFINITION'
        ,*
   FROM sys.objects
 WHERE TYPE_DESC  NOT IN ('SYSTEM_TABLE','INTERNAL_TABLE')