SP and function dependencies

  • Comments posted to this topic are about the item SP and function dependencies

  • i m getting the foll. error:

    Warning: Null value is eliminated by an aggregate or other SET operation.

    my stored proc is calling a scalar valued function.

  • If you are running SQL 2008, you can use sys.sql_expression_dependencies to determine the dependencies.

    We use the following code to build the objects in the correct order. Of course, this can only be run on a database that already has all the objects created, which is usually our Dev instance. This code has been modified a bit because we really use it to generate an ordered code script but this gives you the raw output.

    DECLARE @Cmd varchar(max) = ''

    ;WITH

    SysObjs

    as

    (

    SELECT

    name,

    OBJECT_ID,

    type

    FROM sys.objects

    WHERE type IN ('U','V','P','FN','TF','IF', 'TR')

    )

    ,depends

    as

    (

    SELECT

    so.name as Name,

    sd.referencing_id as ID,

    so.type as Type,

    sdo.object_id as DependsID,

    sd.referenced_entity_name as DependsName,

    sdo.type as DependsType,

    sd.referenced_server_name as DependsServerName,

    sd.referenced_database_name as DependsDatabaseName,

    sd.referenced_schema_name as DependsSchemaName,

    sd.is_caller_dependent as DependsIsCallerDependent,

    sd.is_ambiguous as DependsIsAmbiguous

    FROM sys.sql_expression_dependencies as sd

    LEFT JOIN SysObjs as so

    ON so.object_id = sd.referencing_id

    LEFT JOIN SysObjs as sdo

    ON sdo.object_id = sd.referenced_id OR (sd.referenced_id IS NULL AND sdo.name = sd.referenced_entity_name)

    WHERE ISNULL(sd.referencing_id, 0) <> ISNULL(sd.referenced_id,0)

    )

    , Mapping

    as

    (

    SELECT

    d1.Name,

    d1.ID,

    d1.Type,

    d1.DependsName,

    d1.DependsID,

    d2.DependsType

    FROM depends as d1

    LEFT JOIN depends d2

    ON d1.DependsID = d2.ID

    GROUP BY

    d1.Name,

    d1.ID,

    d1.Type,

    d1.DependsName,

    d1.DependsID,

    d2.DependsType

    )

    , MappingWithBase

    as

    (

    SELECT

    name,

    ID,

    Type,

    DependsName,

    DependsID,

    DependsType

    FROM Mapping

    UNION ALL

    SELECT

    name,

    object_id,

    type,

    NULL,

    NULL,

    NULL

    FROM SysObjs

    WHERE object_id NOT IN (SELECT id FROM Mapping)

    )

    , DepMap

    as

    (

    SELECT

    1 as Level,

    name,

    ID,

    TYPE,

    DependsID

    FROM MappingWithBase

    WHERE ID NOT IN (SELECT DependsID FROM MappingWithBase WHERE DependsID IS NOT NULL)

    GROUP BY Name, ID, Type, DependsID

    UNION ALL

    SELECT

    Level + 1,

    mp.Name,

    mp.ID,

    mp.Type,

    mp.DependsID

    FROM DepMap as dm

    JOIN MappingWithBase as mp

    ON dm.DependsID = mp.ID

    )

    SELECT

    *

    FROM

    (

    SELECT

    DB_NAME() as DBName,

    MAX(COALESCE(so.Name, dm.name)) as Name,

    so.object_id,

    MAX(so.Type) as Type,

    MAX(CASE WHEN dm.Level IS NULL THEN -1 ELSE dm.Level END) as Level,

    DENSE_RANK() OVER (ORDER BY MAX(CASE WHEN dm.Level IS NULL THEN -1 ELSE dm.Level END) DESC) as LevelRank,

    ROW_NUMBER() OVER (ORDER BY MAX(CASE WHEN dm.Level IS NULL THEN -1 ELSE dm.Level END) DESC, MAX(so.Name)) as OrderNo

    FROM DepMap as dm

    FULL OUTER JOIN SysObjs as so

    ON dm.ID = so.object_id

    GROUP BY so.object_ID

    ) as DM

    ORDER BY LevelRank, OrderNo

    /* Anything is possible but is it worth it? */

  • It's just a warning, can you see the function in the results table?

  • Sounds good. No, this was written on 05', I'm sure it's lot better in 08'.

  • Hmm - this does not appear to list the same stored procedures I see with view dependencies?

    And I guess you should truncate Tree between runs?

    Doug

  • what value i have to give to parameter name @sp sysname in the procedure to execute as shown given below

    create procedure dba_GetDependencies1 @sp sysname

  • Thanks for the script.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply