SQL SERVER：如何獲取儲存過程文本排除註釋？

我需要找到所有使用事務的儲存過程，因為我想對這些過程啟用事務中止。但是，——我沒有這樣做；它是繼承的——許多儲存過程包含帶有註釋塊的測試過程，並且大多數測試都包含事務塊。我只對更改實際使用事務的儲存過程感興趣。並且我希望能夠監視儲存的過程何時更新，以便我可以確保設置了這個標誌。

SET XACT_ABORT ON;

附錄：根據評論，這是我係統中的一些範例。

/*
-- clean up after tests
BEGIN TRANSACTION
EXEC dbo.AR_Cleanup_MoveEqualAndOppositeSBPLiabilities
ROLLBACK TRANSACTION
*/

/*
Use case: 147
BEGIN TRANSACTION
....
*/

這是我放在一起的一個解決方案，至少在我的測試中是有效的。

它依賴於 Jeff Moden 的 dbo.DelimitedSplit8K，您可以在此處找到https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80 %9d-函式

您只需將其更改為使用 sys.sql_modules 來代替 procname 和 proccode 部分。

DECLARE @Sample AS TABLE
   (
   procname NVARCHAR(128) NOT NULL
   , proccode NVARCHAR(512) NOT NULL
   )

INSERT INTO @Sample
(procname, proccode)
VALUES ('test1-false'
   , N'/*
-- clean up after tests
BEGIN TRANSACTION
EXEC dbo.AR_Cleanup_MoveEqualAndOppositeSBPLiabilities
ROLLBACK TRANSACTION
*/
Do Stuff
')
, ('test2-true'
   , N'
BEGIN TRANSACTION
EXEC dbo.AR_Cleanup_MoveEqualAndOppositeSBPLiabilities
ROLLBACK TRANSACTION
')
, ('test3-both'
   , N'
/*
-- clean up after tests
BEGIN TRANSACTION
EXEC dbo.AR_Cleanup_MoveEqualAndOppositeSBPLiabilities
ROLLBACK TRANSACTION
*/
BEGIN TRANSACTION
EXEC dbo.AR_Cleanup_MoveEqualAndOppositeSBPLiabilities
ROLLBACK TRANSACTION
')

DECLARE @Split NCHAR(1) = N'
'

/** NOTE: Using dbo.DelimitedSplit8K by Jeff Moden
   from https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function
   */

;WITH CTE_Split AS
   (
   SELECT S.procname
       , T.ItemNumber AS LineNumber
       , T.Item AS Line
       , LineType = CASE   WHEN T.Item LIKE '%/*%' THEN 'CommentStart'
                           WHEN T.Item LIKE '%*/%' THEN 'CommentEnd'
                           WHEN T.Item LIKE '%--%BEGIN TRAN%' THEN 'Commented'
                           WHEN T.Item LIKE '%--%ROLLBACK%' THEN 'Commented'
                           WHEN T.Item LIKE '%--%COMMIT%' THEN 'Commented'
                           WHEN T.Item LIKE '%BEGIN TRAN%' AND NOT(T.Item LIKE '%--%BEGIN TRAN%') THEN 'TransactionStart'
                           WHEN T.Item LIKE '%ROLLBACK%' AND NOT(T.Item LIKE '%--%ROLLBACK%') THEN 'TransactionRollback'
                           WHEN T.Item LIKE '%COMMIT%' AND NOT(T.Item LIKE '%--%COMMIT%') THEN 'TransactiionCommit'
                           ELSE ''
                           END
   FROM @Sample AS S 
       CROSS APPLY [dbo].[DelimitedSplit8K](proccode, @Split) AS T
   )
, CTE_CommentBlocks AS
   (
   SELECT procname, CommentStart, CommentEnd
   FROM (SELECT procname, LineType, LineNumber FROM CTE_Split WHERE LineType IN ('CommentStart', 'CommentEnd')) AS S
       PIVOT (MIN(LineNumber) FOR LineType IN (CommentStart, CommentEnd)) AS P
   )
, CTE_Commented AS
   (
   SELECT S.*
       , CommentedOut = CASE WHEN B.procname IS NOT NULL THEN 'Y' ELSE 'TRANSACTION' END
   FROM CTE_Split AS S
       LEFT OUTER JOIN CTE_CommentBlocks AS B ON B.procname = S.procname AND S.LineNumber BETWEEN B.CommentStart AND B.CommentEnd
   WHERE LineType <> ''
   )
SELECT * FROM CTE_Commented

引用自：https://dba.stackexchange.com/questions/244050