SQL Server 2005存储过程依赖项

下面是一篇关于如何创建UDF的文章。它按照您的建议使用sysobjects和syscomments表。

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

以下是自定义项的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\u依赖关系是准确的。

这不是完全测试过的代码。另外,如果我再这样做的话,我可能会使用informationschema,而不是直接针对系统表。

运行级别告诉您何时可以创建每个对象。因此,可以先运行run\u级别为0的所有对象(在run\u级别内顺序无关紧要),然后运行run\u级别1的所有对象,以此类推。

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