SQL Server 2005 Stored Procedure Dependencies

Question

Hello

I have a list of stored procedures and I'm trying to determine the order they should be implemented in.

Is there a way to determine how the stored procedures are dependent on each other. I'm thinking that I could use sysobjects and syscomments.

Thanks

Answer 1

One way is

EXEC sp_depends 'your_procedure_name'

Answer 2

Here is an article on how to create a UDF that will do this. It makes use of the sysobjects and syscomments tables as you suggested.

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

Here is the SQL for the UDF:

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

Answer 3

Here's some code that I threw together a few weeks ago to build up an ordering of objects for applying to a database based on their dependencies. The first stored procedure just refreshes the metadata so that you can be sure that sql_dependencies will be accurate.

This is not fully tested code. Also, I would probably use INFORMATION_SCHEMA instead of directly going against the system tables if I did this again.

The run_level tells you when each object can be created. So, all objects with a run_level of 0 can be run first (order doesn't matter within a run_level), then all of the objects at run_level 1, etc.

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