SQL Server – How to Get Stored Procedure Text Excluding Comments

commentsparsesql serverstored-procedurestransaction

I need to find all the stored procs that use Transactions, as I want to enable transaction abort to those procedures. However, –I didn't do this; it's inherited– many of the stored procedures contain testing procedures with in comment blocks and most of the tests contain transaction blocks. I am only interested in changing stored procs that actually use transactions. AND I want to be able to monitor when stored procs are updated so that I can make sure that this flag is set.

SET XACT_ABORT ON;

Addendum: based on comments, here're some examples from my system.

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

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

Best Answer

Here is a solution that I put together that worked, at least in my testing.

It relies on dbo.DelimitedSplit8K by Jeff Moden which you can find here https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function

You just need to change it to use sys.sql_modules to sub in for the procname and proccode portions.

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
Related Question