以前龐統曾獻三計給劉備奪川蜀,現在也有三種方法來取 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 欄位 裡的資料,要使用以下方式:
而近來自己實際比較了使用 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'