Post reply

sysdepends table doesn''t populate

  • July 26, 2005 at 6:11 pm

    #94108

    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

    #577349

    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

    #577351

    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 3 (of 3 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy