December 1, 2009 at 1:47 pm
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]
December 1, 2009 at 7:35 pm
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.
December 2, 2009 at 1:02 pm
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"
December 8, 2009 at 11:21 am
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]
December 8, 2009 at 11:32 am
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.
December 8, 2009 at 12:33 pm
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), http://www.red-gate.com/products/SQL_Dependency_Tracker/index.htm, that they built on a suggestion from myself and another founder of SQLServerCentral.
December 8, 2009 at 12:47 pm
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