2017/10/10 - [프로그램 자료/MS-SQL] - [MSSQL] 테이블, 뷰, 프로시저 등의 텍스트 내용 검색 - 'sys.objects', sp_schobject
DB가 점점 복잡해져가면서 안에 내용을 검색해야 할 일이 늘었다.
# 프로시저 내용 검색
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA='DBNAME'
AND ROUTINE_TYPE='PROCEDURE'
AND ROUTINE_DEFINITION LIKE '%검색어%';
# 함수 검색
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA='DBNAME'
AND ROUTINE_TYPE='FUNCTION'
AND ROUTINE_DEFINITION LIKE '%검색어%';
# 칼럼명 검색
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'DBNAME'
AND COLUMN_NAME LIKE '%검색어%';
# 트리거 조회
SELECT *
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = 'DBNAME'
AND ACTION_STATEMENT LIKE '%검색어%';
# 뷰 조회
SELECT *
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'DBNAME'
AND VIEW_DEFINITION LIKE '%검색어%';
다음과 같이 프로시저를 만들어 호출해도 된다.
DELIMITER $$
USE `dbname`$$
DROP PROCEDURE IF EXISTS usp_test$$
CREATE DEFINER=`userid`@`%` PROCEDURE `usp_search`(
IN `condition` varchar(128)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '쿼리 검색 (삭제금지)'
BEGIN
SELECT ROUTINE_TYPE AS TYPE
, ROUTINE_NAME AS NAME
, NULL AS REF
FROM INFORMATION_SCHEMA.ROUTINES
WHERE (ROUTINE_DEFINITION LIKE CONCAT('%', condition, '%') OR ROUTINE_NAME LIKE CONCAT('%', condition, '%'))
AND ROUTINE_SCHEMA = DATABASE()
UNION ALL
SELECT 'TRIGGER' AS TYPE
, TRIGGER_NAME AS NAME
, EVENT_OBJECT_TABLE AS REF
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE (ACTION_STATEMENT LIKE CONCAT('%', condition, '%') OR EVENT_OBJECT_TABLE LIKE CONCAT('%', condition, '%'))
AND TRIGGER_SCHEMA = DATABASE() ;
END$$
DELIMITER ;
