如何在SQL Server 資料庫中 找出符合你的關鍵字的Store Procedure

要讓你在資料庫中，找出符合某關鍵字的 Store Procedure，要怎麼去完成呢？

以前龐統曾獻三計給劉備奪川蜀，現在也有三種方法來取 Store Procedure。

下計：
一個一個點開 Stroe Procedure，並用搜尋的方式，將每個符合你關鍵字的 Store Procedure都記錄下來。

中計：
使用 SQL Server 提供的「產生指令碼」功能，並將所有 Store Procedure都點選，匯出成一個檔案。開啟這檔案，用搜尋的方式找到符合你關鍵字的 Stre Procedure。

上計：
善用SQL Sever 提供的 view ：INFORMATION_SCHEMA.ROUTINES 來完成。

劉備認為上計過急，下計又太緩，故依中計而行。而我早已被 user 逼急了，所以應該會用上計吧！

在資料庫裡，每一支 Store Procedre 或 Function ，都會有對應的一筆 INFORMATION_SCHEMA.ROUTINES紀錄。而這 view 裡的欄位 ROUTINE_DEFINITION 就保存著目前使用者所定義的程式碼。如果今天你想要取得符合某個關鍵字的所有 Store Procedure，那只要在 INFORMATION_SCHEMA.ROUTINES 查詢 ROUTINE_DEFINITION 欄位就可以達成。如果你想更進一步去限制只要查詢Store Procedure類型，那可以在 ROUTINE_TYPE 設定條件為 「PROCEDURE」。(目前有兩種類型：PROCEDURE / FUNCTION)

以下是個範例，找出所有關鍵字為 delete prog_base 的 Store Procedure：

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%prog_base%'
AND ROUTINE_TYPE = 'PROCEDURE';

INFORMATION_SCHEMA.ROUTINES是系統預設提供的 view，換言之，他也是從某些現有的資料表或 view 萃取資料而來。所以，我們當然也可以不透過這 view，直接去從資料表抓。以下，是更原始些的語法，而效果也是一樣。

SELECT distinct sys.sysobjects.name, sys.sysobjects.type
FROM sys.sysobjects INNER JOIN syscomments 
ON sys.sysobjects.id = sys.syscomments.id
WHERE sys.syscomments.text LIKE '%prog_base%' 
AND sys.sysobjects.type = 'P'
ORDER BY sys.sysobjects.NAME

如果要考慮彈性的話，可以考慮使用 syscomments 的使用方法，因為他可以支援的類型，不侷限於 Stroe Procedure , Function，他還支援 Table , View 。所以使用 syscomments 是一個比較完整的解決方案。

而近來自己實際比較了使用 INFORMATION_SCHEMA.ROUTINES 與 syscomments 查詢結果，發現兩邊查出來的資料竟然會不一致，細究原因，原來是 INFORMATION_SCHEMA.ROUTINES 裡的 ROUTINE_DEFINITION 欄位，只支援到長度 4000，換言之，如果有一個 Store Procedure 寫了超過 4000 字以上的程式碼，剛好你的查詢關鍵字又落到 4000 字以後，這樣使用 INFORMATION_SCHEMA.ROUTINES 就查不出資料了。發現了這原因後，就更建議使用 syscomments 來完成你的需求了。詳細原因可參考 Madhivanan 部落格的介紹。他建議若要完整取得 ROUTINE_DEFINITION 欄位 裡的資料，要使用以下方式：

--Method 1
select object_definition(object_id('your_procedure'))

--Method 2
EXEC sp_helptext 'your_procedure'

參考：
01：http://www.mssqltips.com/tip.asp?tip=1419
02：http://www.eggheadcafe.com/community/aspnet/13/10148148/finding-a-keyword-in-the-stored-procedure.aspx
03：http://beyondrelational.com/blogs/madhivanan/archive/2010/02/02/routine-definition-from-information-schema-routines.aspx