[mssql] 특정 단어가 들어간 프러시저, 트리거, 뷰, 에이전트,펑션,함수 작업 찾기(search function,view,agent,trigger,procedure)

mssql search function, view, agent, trigger, procedure.

USE [MemberDB]; -- DB 설정

#프러시저 찾기 (프러시저 내용에서 찾기)

DECLARE @search_text VARCHAR(MAX) = 'Member';

SELECT ROUTINE_NAME 

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE '%'+@search_text +'%'

AND ROUTINE_TYPE='PROCEDURE'

order by ROUTINE_NAME;

;

# Function 에서 찾기

DECLARE @search_text VARCHAR(MAX) = 'member';

SELECT ROUTINE_NAME 

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE '%'+@search_text +'%'

AND ROUTINE_TYPE='FUNCTION'

order by ROUTINE_NAME;

;

# 트리거 찾기 (트리거 내용에서 찾기)

DECLARE @search_text VARCHAR(MAX) = 'Member';

SELECT  *

FROM    (

            SELECT

                    db_name() AS db_name,

                    tbl.name AS tbl_name,

                    tr.name AS tr_name,

                    ISNULL(smtr.definition, ssmtr.definition) AS [Definition]

            FROM

                    sys.tables AS tbl

                    INNER JOIN sys.objects AS tr ON (tr.type in ('TR', 'TA')) AND (tr.parent_object_id=tbl.object_id)

                    LEFT OUTER JOIN sys.assembly_modules AS mod ON mod.object_id = tr.object_id

                    LEFT OUTER JOIN sys.sql_modules AS smtr ON smtr.object_id = tr.object_id

                    LEFT OUTER JOIN sys.system_sql_modules AS ssmtr ON ssmtr.object_id = tr.object_id

        ) AS A

WHERE Definition like '%'+ @search_text +'%'

;

# view 뷰 찾기 (뷰 내용에서 찾기)

DECLARE @search_text VARCHAR(MAX) = 'PointAdd';

select svie.name AS view_name, svie.type, svie.type_desc, scomm.text

from sys.views AS svie 

INNER JOIN sys.syscomments AS scomm ON svie.object_id = scomm.id

where scomm.text LIKE '%'+@search_text +'%'

;

# SQL Server 에이전트 작업 찾기 (작업 내용에서 찾기)

DECLARE @search_text VARCHAR(MAX) = 'cuptop';

select 

A.name AS job_name, A.enabled AS isUse, A.description,

B.step_id, B.step_name, B.database_name, B.command

FROM msdb.dbo.sysjobs A

INNER JOIN msdb.dbo.sysjobsteps B ON A.job_id = B.job_id

WHERE B.command LIKE '%'+@search_text+'%'

ORDER BY A.name ASC, B.step_id ASC