SQL Server 2005的存储过程依赖

问题描述：

我的存储过程的列表，我试图确定他们应该在执行顺序。SQL Server 2005的存储过程依赖

有没有一种方法来确定存储过程是如何互相依赖。我在想我可以使用sysobjects和syscomments。

感谢

答

这里是如何创建一个UDF，将做到这一点的文章。它按照您的建议使用sysobjects和syscomments表。

http://www.eggheadcafe.com/community/aspnet/13/12562/list-sql-stored-procedure.aspx

下面是UDF的SQL：

CREATE FUNCTION dbo.uf_FindProcedureDependencies(@ProcName varchar(256)) 
     RETURNS @blah TABLE (depth tinyint not null, 
     tree varchar(7700) COLLATE SQL_Latin1_General_CP437_BIN not null, 
     objectname varchar(256) COLLATE SQL_Latin1_General_CP437_BIN not null, 
     dependencytype varchar(16) COLLATE SQL_Latin1_General_CP437_BIN not null) 
AS 
BEGIN 
    DECLARE @depth tinyint 
    SELECT @depth = 1 

    INSERT @blah (depth, tree, objectname, dependencytype) 
     SELECT DISTINCT @depth, @ProcName + ' -> ' + so2.name, so2.name, 'stored procedure' FROM 
      sysobjects so1, syscomments sc1, sysobjects so2, syscomments sc2 WHERE 
      so1.id = sc1.id AND so2.id = sc2.id AND so1.id <> so2.id AND so1.type = 'P' AND so2.type = 'P' 
      AND so1.name = @ProcName AND REPLACE(sc1.text,so1.name,'') LIKE '%EXEC%' + so2.name + '%' 

    WHILE (@@ROWCOUNT > 0) 
    BEGIN 
     SET @depth = @depth + 1 
     INSERT @blah (depth, tree, objectname, dependencytype) 
      SELECT DISTINCT @depth, b.objectname + ' -> ' + so2.name, so2.name, 'stored procedure' FROM 
      sysobjects so1, syscomments sc1, sysobjects so2, syscomments sc2, @blah b WHERE 
      so1.id = sc1.id AND so2.id = sc2.id AND so1.id <> so2.id AND so1.type = 'P' AND so2.type = 'P' 
      AND so1.name = b.objectname AND REPLACE(sc1.text,so1.name,'') LIKE '%EXEC%' + so2.name + '%' 
      AND b.depth = @depth - 1 
    END 

    RETURN 
END

答

一种方法是

EXEC sp_depends 'your_procedure_name'

答

下面是一些代码，我在几个星期前扔起来，构成了对象的排序：施用于数据库基于其依赖。第一个存储过程只是刷新元数据，以便确保sql_dependencies准确无误。

这是没有完全测试的代码。另外，如果我再次这样做，我可能会使用INFORMATION_SCHEMA而不是直接针对系统表。

run_level告诉你何时可以创建每个对象。所以，为0的run_level所有对象都可以首先在run_level 1运行（顺序并不一个run_level内关系），则所有的对象，等

IF (OBJECT_ID('dbo.RefreshSqlModuleForAllObjs') IS NOT NULL) 
    DROP PROCEDURE dbo.RefreshSqlModuleForAllObjs 
GO 
CREATE PROCEDURE dbo.RefreshSqlModuleForAllObjs 
AS 
BEGIN 
    DECLARE 
     @object_id  INT, 
     @schema_name SYSNAME, 
     @object_name SYSNAME, 
     @full_name  NVARCHAR(776) 

    DECLARE cur_objects CURSOR FOR 
     SELECT 
      object_id, 
      OBJECT_SCHEMA_NAME(object_id), 
      OBJECT_NAME(object_id) 
     FROM 
      sys.objects 
     WHERE 
      type IN ('P', 'TR', 'V', 'FN', 'TF', 'IF') 

    OPEN cur_objects 

    FETCH NEXT FROM cur_objects INTO @object_id, @schema_name, @object_name 

    WHILE (@@FETCH_STATUS = 0) 
    BEGIN 
     SET @full_name = QUOTENAME(@schema_name) + '.' + QUOTENAME(@object_name) 

     EXEC sp_refreshsqlmodule @full_name 

     FETCH NEXT FROM cur_objects INTO @object_id, @schema_name, @object_name 
    END 

    CLOSE cur_objects 

    DEALLOCATE cur_objects 
END 
GO

IF (OBJECT_ID('dbo.GenObjApplyOrder') IS NOT NULL) 
    DROP PROCEDURE dbo.GenObjApplyOrder 
GO 
CREATE PROCEDURE dbo.GenObjApplyOrder 
AS 
BEGIN 
    DECLARE 
     @i INT, 
     @count INT 

    DECLARE 
     @run_order TABLE (object_id INT, run_level SMALLINT) 

    SET @i = 0 
    SET @count = 1 

    EXEC dbo.RefreshSqlModuleForAllObjs 

    WHILE (@count > 0) 
    BEGIN 
     INSERT INTO @run_order (object_id, run_level) 
     SELECT 
      object_id, 
      @i 
     FROM 
      sys.objects o 
     WHERE 
      NOT EXISTS (SELECT * FROM sys.sql_dependencies d WHERE d.object_id = o.object_id AND d.referenced_major_id NOT IN (SELECT object_id FROM @run_order)) AND 
      NOT EXISTS (SELECT * FROM @run_order ro WHERE ro.object_id = o.object_id) AND 
      o.type IN ('U', 'P', 'V', 'TR', 'TF', 'IF', 'FN') 

     SELECT @count = @@ROWCOUNT 

     SELECT @i = @i + 1 
    END 

    INSERT INTO @run_order (object_id, run_level) 
    SELECT 
     o.object_id, 
     -999 
    FROM 
     sys.objects o 
    WHERE 
     NOT EXISTS (SELECT * FROM @run_order ro WHERE ro.object_id = o.object_id) AND 
     o.type IN ('U', 'P', 'V', 'TR', 'TF', 'IF', 'FN') 

    SELECT OBJECT_NAME(object_id), run_level FROM @run_order ORDER BY ABS(run_level) 
END 
GO

SQL Server 2005的存储过程依赖

相关推荐