Dependencies not showing up

  • In trying to clean up my db, I recently deleted a view because it showed no dependencies.

    Well, the next day I started getting errors, because this view is, in fact, used.

    The view I deleted was ForecastNormalizedExploded1, whish is quite obviously used in the following view, named FE2a.

    SELECT dbo.LatestRevMaster1.smtsetupname, dbo.ForecastNormalizedExploded1.Noun, dbo.ForecastNormalizedExploded1.BOM1, dbo.BackLog.BackLogQty,

    dbo.ForecastNormalizedExploded1.AvgDailyFcst AS forecastdailydemand,

    (dbo.ForecastNormalizedExploded1.AvgDailyFcst * MIN(dbo.constants.ForecastWeeks) * 5 + ISNULL(MIN(dbo.BackLog.BackLogQty), 0))

    / (MIN(dbo.constants.ForecastWeeks) * 5) AS DailyDemandPlusBacklog, dbo.DemandHistoryStdDev.DailyStdDev AS DemandHistoryStdDev,

    dbo.SMT_ABC.ABC, dbo.SafetyStockConfLevels.LowWIPFlag, dbo.SafetyStockConfLevels.HighWIPFlag, dbo.WIP3.WIPTotal,

    CASE WHEN MIN(CONVERT(float, isnull([dailystddev], 0))) <= 0 THEN SUM([avgdailyFCST]) ELSE Sqrt(MIN(isnull([ltadjusted], 1)) / 24)

    * MIN(isnull([lowwipsigma], 3)) * MIN([dailystddev]) END AS SafetyStock, CASE WHEN CASE WHEN MIN(CONVERT(float, isnull([dailystddev], 0)))

    < 0 THEN 10 * SUM([avgdailyFCST]) ELSE Sqrt(MIN(isnull([ltadjusted], 1)) / 24) * MIN(isnull([highwipsigma], 6)) * MIN([dailystddev])

    END < MIN(isnull([ltadjusted], 1)) / 24 * (([AvgDailyFCST]) * MIN([forecastweeks]) * 5 + isnull(MIN([backlogqty]), 0)) / (MIN([forecastweeks]) * 5)

    THEN MIN(isnull([ltadjusted], 1)) / 24 * (([AvgDailyFCST]) * MIN([forecastweeks]) * 5 + isnull(MIN([backlogqty]), 0)) / (MIN([forecastweeks]) * 5)

    ELSE CASE WHEN MIN(CONVERT(float, isnull([dailystddev], 0))) < 0 THEN 10 * SUM([avgdailyFCST]) ELSE Sqrt(MIN(isnull([ltadjusted], 1)) / 24)

    * MIN(isnull([highwipsigma], 6)) * MIN([dailystddev]) END END AS WIPLimit, dbo.WIP3.WIPNonPCA

    FROM dbo.ForecastNormalizedExploded1 INNER JOIN

    dbo.LatestRevMaster1 LEFT OUTER JOIN

    dbo.SMT_ABC ON dbo.LatestRevMaster1.smtsetupname = dbo.SMT_ABC.SMTSetupName LEFT OUTER JOIN

    dbo.SafetyStockConfLevels ON dbo.SMT_ABC.ABC = dbo.SafetyStockConfLevels.ABC AND

    dbo.SMT_ABC.ValueStream = dbo.SafetyStockConfLevels.ValueStream LEFT OUTER JOIN

    dbo.LeadTimes ON dbo.SMT_ABC.ValueStream = dbo.LeadTimes.ValueStream AND dbo.SMT_ABC.ABC = dbo.LeadTimes.ABC ON

    dbo.ForecastNormalizedExploded1.Noun = dbo.LatestRevMaster1.Noun LEFT OUTER JOIN

    dbo.WIP3 ON dbo.ForecastNormalizedExploded1.Noun = dbo.WIP3.Noun INNER JOIN

    dbo.constants ON dbo.LatestRevMaster1.ValueStream = dbo.constants.ValueStream LEFT OUTER JOIN

    dbo.BackLog ON dbo.ForecastNormalizedExploded1.Noun = dbo.BackLog.Noun LEFT OUTER JOIN

    dbo.DemandHistoryNormalized ON dbo.ForecastNormalizedExploded1.BOM1 = dbo.DemandHistoryNormalized.DemandItem LEFT OUTER JOIN

    dbo.DemandHistoryStdDev ON dbo.ForecastNormalizedExploded1.BOM1 = dbo.DemandHistoryStdDev.DemandItem

    GROUP BY dbo.LatestRevMaster1.smtsetupname, dbo.ForecastNormalizedExploded1.Noun, dbo.ForecastNormalizedExploded1.BOM1,

    dbo.BackLog.BackLogQty, dbo.ForecastNormalizedExploded1.AvgDailyFcst, dbo.DemandHistoryStdDev.DailyStdDev, dbo.SMT_ABC.ABC,

    dbo.SafetyStockConfLevels.LowWIPFlag, dbo.SafetyStockConfLevels.HighWIPFlag, dbo.WIP3.WIPTotal, dbo.WIP3.WIPNonPCA

    But when I right click on ForecastNormalizedExploded1 and view dependencies, the only thing that shows up is itself.

    Conversely, if I rightclick on FE2a and view objects on which FE2a depends, ForecastNormalizedExploded1 does not show up, although all of the other objects used by the view do show up. What gives???

    [font="Comic Sans MS"]She Through Whom All Data Flows[/font]

  • SQL Server tracks dependencies (e.g. in sys.sysdepends) if the referenced objects exist when referencing object is created. The information in sys.sysdepends is only used for documentation purposes - it is not used in any part of query execution. All objects don't actually need to exist until the referencing object (e.g. FE2a) is used. When you check for dependencies, Management Studio is actually queries data in sys.sysdepends.

    This is "deferred name resolution".

    Even if the referenced objects are subsequently created, no addition record(s) are created in sys.sysdepends.

    You should only use the data in sys.sysdepends as a guide.

  • In addition to what has already been said, I would also think that you would never be able to get from SSMS had a dependancy on the view to an application. It is only going to be able to show you objects that are in the instance. So if any application depends on the view you will never see it.

    Joie Andrew
    "Since 1982"

  • So this seems to me to mean that the dependencies thing is undependable and therefore, basically useless.


    Is there a better (ie, dependable) way to see dependencies in SQL server? In MS Access, there is at least an add-in (questica query dependencies) that is dependable.

    [font="Comic Sans MS"]She Through Whom All Data Flows[/font]

  • It is what it is. How sysdepends was implemented did not account for recording updates where depentant objects were created later. This has, iirc, been corrected in SQL Server 2008.

  • I'm not sure if it's been corrected or not, but you cannot necessarily depend on sys.depends. Just writing that sounds silly, and it's frustrated many of us for years.

    Red Gate has a tool to fix this ( I work for Red GAte),, that they built on a suggestion from myself and another founder of SQLServerCentral.

  • I usually do a character string search to see where an object is used. I don't know how dependable it is either, but better than sys.depends.

    Something like:

    select name, create_date, CHARINDEX('MyObjectName', definition),substring(definition,CHARINDEX('MyObjectName', definition)-25,150) as 'Code Snippet', definition as 'Entire Code'

    from sys.sql_modules com

    join sys.objects obj on com.object_id = obj.object_id

    where definition like '%MyObjectName%'

    order by name

    It's called sys.depends because it depends on whether or not objects existed at the time.

Viewing 7 posts - 1 through 6 (of 6 total)

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