我需要找到使用事务的所有存储过程,因为我希望启用事务中止到这些过程。然而,--我没有这么做;它是继承的--许多存储过程包含带有注释块的测试过程,而大多数测试包含事务块。我只对更改实际使用事务的存储过程感兴趣。我希望能够监视存储的进程何时更新,以确保设置了此标志。
SET XACT_ABORT ON;增编:根据评论,这里有一些来自我的系统的例子。
/*
-- clean up after tests
BEGIN TRANSACTION
EXEC dbo.AR_Cleanup_MoveEqualAndOppositeSBPLiabilities
ROLLBACK TRANSACTION
*/
/*
Use case: 147
BEGIN TRANSACTION
....
*/发布于 2019-07-30 13:12:35
这里有一个解决方案,我把它放在一起,至少在我的测试中是有效的。
它依赖于杰夫·莫登的dbo.DelimitedSplit8K,你可以在这里找到它,https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function
您只需更改它,就可以使用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_Commentedhttps://dba.stackexchange.com/questions/244050
复制相似问题