星期五, 3月 21, 2014

[SQL] 查詢 Store Procedure 內容

利用 sys.sql_modules 來查詢 Store Procedure 內容,但假如 Store Procedure 有加密,definition 欄位回傳值是 NULL,就無法解析內容
DECLARE @SPContent TABLE (SPName sysname , [definition] nvarchar(max))

INSERT INTO @SPContent (SPName,[definition])
SELECT 
  O.name AS SPName, 
  SM.[definition]
FROM sys.sql_modules as SM
  JOIN sys.objects AS O ON SM.[object_id] = O.[object_id]
WHERE O.type = 'P';

SELECT * FROM @SPContent WHERE [definition] LIKE '%keyword%'

沒有留言:

張貼留言