July 26, 2005 at 6:11 pm
Can somebody tell me the different reasons why sysdepends table doesn't get populated when we compile a SP.
One obvious reason is that dependent objects are not present at the time of parsing.
what are the other possible reasons?
I found a stored procedure recently which has reference to about 10 tables 1 table values function and 3 temporary tables.
stored procedure runs without any problem and i have re-created it several times but still i cannot get the sysdepends table populated for this SP.
I cannot publish the SP becasue it has some sensitive information, that's why i am asking the general rules that are used to populate the sysdepends table.
July 26, 2005 at 6:37 pm
The case you've cited is the main one. Is there a reason why the stored procedure is being created prior to the objects it references? Perhaps you can re-order the object creation. Other than that, is it a big concern? The sysdepends table doesn't have to be accurate for everything to work correctly (though if it is accurate it helps on reporting dependencies).
K. Brian Kelley
@kbriankelley
July 26, 2005 at 6:45 pm
Hi Brian,
thanks for your input. I found another reason , which probably is the reason why it's not getting populated in my case.
when you have a join to a temparary table (# table) sysdepends won't pick the actual table - participated in the join with temp table - as a dependent object. see below for an example. in this case only Region table will be picked up by the sysdepends and not the Categories.
create procedure a_test
as
create table #temp (id int)
insert into #temp
select 1
SELECT CategoryID, CategoryName, Description, Picture
FROM dbo.Categories a inner join #temp b
on a.CategoryID = b.ID
where CategoryID > 2
SELECT RegionID, RegionDescription
FROM dbo.Region
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply