[MS SQL] 查詢包含特定字串之Proc、Function、View

--查詢包含特定字串之Proc、Function
DECLARE @TmpStr NVARCHAR(MAX)='Factory=@CorpNo'

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%'+@TmpStr+'%'
AND ROUTINE_TYPE ROUTINE_TYPE IN ('PROCEDURE','FUNCTION')

--查詢包含特定字串之View
SELECT TABLE_NAME,VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE '%'+@TmpStr+'%'

參考自-青年廣論研討班-如何在SQL Server 資料庫中 找出符合你的關鍵字的Store Procedure