T-SQL Puzzler - 抓取对象依赖项 [英] T-SQL Puzzler - Crawling Object Dependencies

问题描述

此代码涉及递归存储过程调用和避免游标名称冲突的不太好"的方法.最后我不在乎它是否使用游标.只是寻找最优雅的方法.我主要将它用作跟踪存储过程层次结构的简单方法(无需购买产品).我在动态 sql"中尝试了游标，但运气不佳.我想深入 10 级.

所需的输出:

<前>sp_Master_Proc_Name-- sp_Child_Proc_1_Name---- sp_Sub_Proc_1_Name-- sp_Child_Proc_2_Name-- sp_Child_Proc_3_Name

它不漂亮，但这是代码(它没有按预期工作)

 CREATE PROCEDURE SP_GET_DEPENDENCIES(@obj_name varchar(300),@level 整数)作为声明@sub_obj_name varchar(300)如果@level = 1开始打印@obj_name结尾如果@level = 1开始声明 the_cursor_1 CURSOR FORSELECT DISTINCT REPLICATE('--', @level) + ' ' + c.name FROM dbo.sysdepends aINNER JOIN dbo.sysobjects b ON a.id = b.idINNER JOIN dbo.sysobjects c ON a.depid = c.id哪里 b.name = @obj_name打开 the_cursor_1SET @level = @level + 1FETCH NEXT FROM the_cursor_1 INTO @sub_obj_name而@@FETCH_STATUS = 0开始打印@sub_obj_nameEXEC SP_GET_DEPENDENCIES @sub_obj_name, @levelFETCH NEXT FROM the_cursor_1 INTO @sub_obj_name结尾关闭_cursor_1解除分配 the_cursor_1结尾如果@level = 2开始声明 the_cursor_2 CURSOR FORSELECT DISTINCT REPLICATE('--', @level) + ' ' + c.name FROM dbo.sysdepends aINNER JOIN dbo.sysobjects b ON a.id = b.idINNER JOIN dbo.sysobjects c ON a.depid = c.id哪里 b.name = @obj_name打开 the_cursor_2SET @level = @level + 1FETCH NEXT FROM the_cursor_2 INTO @sub_obj_name而@@FETCH_STATUS = 0开始打印@sub_obj_nameEXEC SP_GET_DEPENDENCIES @sub_obj_name, @levelFETCH NEXT FROM the_cursor_2 INTO @sub_obj_name结尾关闭_cursor_2解除分配 the_cursor_2结尾如果@level = 3开始声明 the_cursor_3 CURSOR FORSELECT DISTINCT REPLICATE('--', @level) + ' ' + c.name FROM dbo.sysdepends aINNER JOIN dbo.sysobjects b ON a.id = b.idINNER JOIN dbo.sysobjects c ON a.depid = c.id哪里 b.name = @obj_name打开 the_cursor_3设置@level = @level + 1FETCH NEXT FROM the_cursor_3 INTO @sub_obj_name而@@FETCH_STATUS = 0开始打印@sub_obj_nameEXEC SP_GET_DEPENDENCIES @sub_obj_name, @levelFETCH NEXT FROM the_cursor_3 INTO @sub_obj_name结尾关闭_cursor_3解除分配 the_cursor_3结尾

解决方案

对于 ms sql server 你可以使用 CURSOR LOCAL，那么游标对于 sproc 调用是本地的，你的代码就会变得更简单:

CREATE PROCEDURE uspPrintDependencies(@obj_name varchar(300),@level 整数)作为设置无计数声明@sub_obj_name varchar(300)如果@level >0 开始PRINT Replicate(' ',@level) + @obj_name结尾否则开始打印@obj_name结尾DECLARE myCursor CURSOR LOCAL FOR选择不同的 c.nameFROM dbo.sysdepends aINNER JOIN dbo.sysobjects b ON a.id = b.idINNER JOIN dbo.sysobjects c ON a.depid = c.id哪里 b.name = @obj_name打开我的光标SET @level = @level + 1FETCH NEXT FROM myCursor INTO @sub_obj_name而@@FETCH_STATUS = 0 开始EXEC uspPrintDependencies @sub_obj_name, @levelFETCH NEXT FROM myCursor INTO @sub_obj_name结尾关闭我的光标释放我的光标去

This code involves a recursive Stored Procedure call and a "not so great" method of avoiding cursor name collision. In the end I don't care if it uses cursors or not. Just looking for the most elegant approach. I'm mainly going to use it as a simple method to track down Stored Proc hierarchies (without buying a product). I tried cursors within "dynamic sql" and didn't have much luck. I'd like to go about 10 levels deep.

The desired output:

sp_Master_Proc_Name  
-- sp_Child_Proc_1_Name  
---- sp_Sub_Proc_1_Name    
-- sp_Child_Proc_2_Name  
-- sp_Child_Proc_3_Name

Its not pretty, but here is the code (and it didn't work as expected)

    CREATE PROCEDURE SP_GET_DEPENDENCIES
    (
      @obj_name varchar(300),
      @level int
    )
    AS
    DECLARE @sub_obj_name varchar(300)
    IF @level = 1
      BEGIN
        PRINT @obj_name
      END

    IF @level = 1
      BEGIN 
        DECLARE the_cursor_1 CURSOR FOR 
            SELECT DISTINCT REPLICATE('--', @level) + ' ' + c.name FROM dbo.sysdepends a
              INNER JOIN dbo.sysobjects b ON a.id = b.id
              INNER JOIN dbo.sysobjects c ON a.depid = c.id
              WHERE b.name = @obj_name
        OPEN the_cursor_1
        SET @level = @level + 1
        FETCH NEXT FROM the_cursor_1 INTO @sub_obj_name 
        WHILE @@FETCH_STATUS = 0 
          BEGIN 
            PRINT @sub_obj_name
            EXEC SP_GET_DEPENDENCIES @sub_obj_name, @level 
            FETCH NEXT FROM the_cursor_1 INTO @sub_obj_name 
          END
        CLOSE the_cursor_1
        DEALLOCATE the_cursor_1
      END

    IF @level = 2
      BEGIN 
        DECLARE the_cursor_2 CURSOR FOR 
            SELECT DISTINCT REPLICATE('--', @level) + ' ' + c.name FROM dbo.sysdepends a
              INNER JOIN dbo.sysobjects b ON a.id = b.id
              INNER JOIN dbo.sysobjects c ON a.depid = c.id
              WHERE b.name = @obj_name
        OPEN the_cursor_2
        SET @level = @level + 1
        FETCH NEXT FROM the_cursor_2 INTO @sub_obj_name 
        WHILE @@FETCH_STATUS = 0 
          BEGIN 
            PRINT @sub_obj_name
            EXEC SP_GET_DEPENDENCIES @sub_obj_name, @level 
            FETCH NEXT FROM the_cursor_2 INTO @sub_obj_name 
          END
        CLOSE the_cursor_2
        DEALLOCATE the_cursor_2
      END

    IF @level = 3
      BEGIN 
        DECLARE the_cursor_3 CURSOR FOR 
            SELECT DISTINCT REPLICATE('--', @level) + ' ' + c.name FROM dbo.sysdepends a
              INNER JOIN dbo.sysobjects b ON a.id = b.id
              INNER JOIN dbo.sysobjects c ON a.depid = c.id
              WHERE b.name = @obj_name
        OPEN the_cursor_3
        SET @level = @level + 1
        FETCH NEXT FROM the_cursor_3 INTO @sub_obj_name 
        WHILE @@FETCH_STATUS = 0 
          BEGIN 
            PRINT @sub_obj_name
            EXEC SP_GET_DEPENDENCIES @sub_obj_name, @level 
            FETCH NEXT FROM the_cursor_3 INTO @sub_obj_name 
          END
        CLOSE the_cursor_3
        DEALLOCATE the_cursor_3
      END

解决方案

for ms sql server you can use CURSOR LOCAL, then the cursor is local to the sproc call and your code becomes much simpler:

CREATE PROCEDURE uspPrintDependencies
(
    @obj_name varchar(300),
    @level int
)
AS
SET NOCOUNT ON
DECLARE @sub_obj_name varchar(300)

if @level > 0 begin
    PRINT Replicate(' ',@level) + @obj_name
end
else begin
    PRINT @obj_name
end

DECLARE myCursor CURSOR LOCAL FOR 
    SELECT 
        DISTINCT c.name 
    FROM dbo.sysdepends a
        INNER JOIN dbo.sysobjects b ON a.id = b.id
        INNER JOIN dbo.sysobjects c ON a.depid = c.id
    WHERE b.name = @obj_name
OPEN myCursor
SET @level = @level + 1
FETCH NEXT FROM myCursor INTO @sub_obj_name 
WHILE @@FETCH_STATUS = 0 BEGIN 
    EXEC uspPrintDependencies @sub_obj_name, @level 
    FETCH NEXT FROM myCursor INTO @sub_obj_name 
END
CLOSE myCursor
DEALLOCATE myCursor
GO

这篇关于T-SQL Puzzler - 抓取对象依赖项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！