首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >SQL SERVER:如何获取存储过程文本(不包括注释)?

问SQL SERVER:如何获取存储过程文本(不包括注释)?
EN

Database Administration用户
提问于 2019-07-29 15:42:50
回答 1查看 1.2K关注 0票数 0

我需要找到使用事务的所有存储过程,因为我希望启用事务中止到这些过程。然而,--我没有这么做;它是继承的--许多存储过程包含带有注释块的测试过程,而大多数测试包含事务块。我只对更改实际使用事务的存储过程感兴趣。我希望能够监视存储的进程何时更新,以确保设置了此标志。

代码语言:javascript
复制
SET XACT_ABORT ON;

增编:根据评论,这里有一些来自我的系统的例子。

代码语言:javascript
复制
/*
-- clean up after tests
BEGIN TRANSACTION
EXEC dbo.AR_Cleanup_MoveEqualAndOppositeSBPLiabilities
ROLLBACK TRANSACTION
*/

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

回答 1

Database Administration用户

回答已采纳

发布于 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部分。

代码语言:javascript
复制
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
票数 2
EN
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://dba.stackexchange.com/questions/244050

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档