可以在T-SQL代码中检测游标（嵌套游标）吗？

I'm hoping to find a way to sniff out potentially inefficient T-SQL within stored procedures, in this case detecting not just cursors in stored procedures, but preferably nested cursors.

我希望找到一种方法来嗅出存储过程中可能效率低下的T-SQL,在这种情况下,不仅要检测存储过程中的游标,还要检测嵌套游标。

With the script below based on sys.dm_sql_referenced_entities, from a given starting stored procedure I can see a recursive downstream call stack, including a column indicating whether the text CURSOR was found within the procedure definition.

使用下面基于sys.dm_sql_referenced_entities的脚本,从给定的起始存储过程我可以看到一个递归的下游调用堆栈,包括一个列,指示是否在过程定义中找到了文本CURSOR。

This is helpful, but it isn't capable of telling me:

这很有帮助,但它无法告诉我:

1. whether more than one cursor exists within a procedure

过程中是否存在多个游标

2. whether nested cursors are used (and of course, the truly perfect solution for that would also have to detect a call to stored procedure containing a cursor, from within a cursor)

是否使用嵌套游标(当然,真正完美的解决方案还必须检测从游标内对包含游标的存储过程的调用)

Being able to do this I think is probably beyond the abilities of querying sys tables, and involves parsing the SQL itself - does anyone know of a technique or tool that could accomplish this, or perhaps an entirely different approach that could tell me the same information.

能够这样做我认为可能超出查询sys表的能力,并涉及解析SQL本身 - 有没有人知道可以实现这一点的技术或工具,或者可能是一个完全不同的方法,可以告诉我相同的信息。

DECLARE @procname varchar(30)
SET @procname='dbo.some_root_procedure_name'
;WITH CTE([DB],[OBJ],[INDENTED_OBJ],[SCH],[lvl],[indexof_cursor],[referenced_object_definition])
AS
(
    SELECT referenced_database_name AS [DB],referenced_entity_name AS [OBJ],
    cast(space(0) + referenced_entity_name as varchar(max)) AS [INDENTED_OBJ],
    referenced_schema_name AS [SCH],0 AS [lvl]
    ,charindex('cursor',object_definition(referenced_id)) as indexof_cursor
    ,object_definition(referenced_id) as [referenced_object_definition]
    FROM sys.dm_sql_referenced_entities(@procname, 'OBJECT') 
    INNER JOIN sys.objects as o on o.object_id=OBJECT_ID(referenced_entity_name)
    WHERE o.type IN ('P','FN','IF','TF') 
UNION ALL 
    SELECT referenced_database_name AS [DB],referenced_entity_name AS [OBJ],
    cast(space(([lvl]+1)*2) + referenced_entity_name as varchar(max)) AS [INDENTED_OBJ],
    referenced_schema_name AS [SCH],[lvl]+1 as [lvl]
    ,charindex('cursor',object_definition(referenced_id)) as indexof_cursor
    ,object_definition(referenced_id) as [referenced_object_definition]
    FROM CTE as c CROSS APPLY
    sys.dm_sql_referenced_entities(c.SCH+'.'+c.OBJ, 'OBJECT') as ref
    INNER JOIN sys.objects as o on o.object_id=OBJECT_ID(referenced_entity_name)
    WHERE o.type IN ('P','FN','IF','TF') and ref.referenced_entity_name NOT IN (c.OBJ)  -- Exit Condition
) 
SELECT 
* 
FROM CTE

EDIT: I am marking this as "solved" even though I think some improvements could be made to the below solution - I think it is "good enough" for most scenarios, but I think a fully recursive solution that can traverse an "infinitely" deep call chain is possible.

编辑:我将此标记为“已解决”,即使我认为可以对以下解决方案进行一些改进 - 我认为对于大多数情况来说它“足够好”,但我认为可以遍历“无限”的完全递归解决方案深呼叫链是可能的。

1 个解决方案

#1

Maybe there is a more efficient way, but you could search the procedure code. It's not foolproof though in that it could get some false positives, but you shouldn't miss any. It doesn't ignore comments and variable names so it's quite possible to pick up some extra stuff.

也许有一种更有效的方法,但您可以搜索过程代码。它并非万无一失,因为它可能会产生一些误报,但你不应该错过任何一个。它不会忽略注释和变量名称,因此很有可能获得一些额外的东西。

SELECT name, xtype, colid, text
into #CodeBlocks
FROM dbo.sysobjects left join .dbo.syscomments 
ON dbo.sysobjects.id = .dbo.syscomments.id
where xtype = 'P'
order by 1

SELECT name,
        (SELECT convert(varchar(max),text)  
        FROM #CodeBlocks t2
        WHERE t1.name = t2.name  
        ORDER BY t2.colid
        FOR XML PATH('')
        ) text
into #AllCode
FROM #CodeBlocks t1
GROUP BY name

select #AllCode.name,
    case when InterProc.name is not null then
        'Possible Inter-Proc Nesting'
    when #AllCode.text like '%CURSOR%FOR%CURSOR%FOR%DEALLOCATE%DEALLOCATE%' then
        'Possible Nested Cursor'
    when #AllCode.text like '%CURSOR%FOR%CURSOR%FOR%' then
        'Possible Multiple Cursor Used'
    ELSE
        'Possible Cursor Used'
    end
from #AllCode
left join #AllCode InterProc
on InterProc.text like '%CURSOR%FOR%'
    and #AllCode.text like '%CURSOR%FOR%' + InterProc.name + '%DEALLOCATE%'
where #AllCode.text like '%CURSOR%FOR%'

I found a few nested cursors on our server I didn't know about. Interesting. :)

我在服务器上发现了一些我不知道的嵌套游标。有趣。 :)