Dependencies are Gone after SmartRename

erbentrauterbentraut Posts: 7
edited January 24, 2007 1:56PM in SQL Refactor Previous Versions
I have a SQL 2k db for which I am renaming several tables that to my knowledge have never been previously renamed through EM or the sp_rename stored procedure. After running a smart rename on the table which there are several dependencies (stored procs, views, etc). The depenencies are gone after running the rename script. The rename does change all the depenant objects correctly, and if I recompile the SP's or resave the views, the dependencies re-appear.

Is this a known issue or something I could be doing wrong? Any help is appreciated.

Thanks
Steve Erbentraut
· Share on Twitter

Comments

  • Andras BAndras B Posts: 249
    erbentraut wrote:
    I have a SQL 2k db for which I am renaming several tables that to my knowledge have never been previously renamed through EM or the sp_rename stored procedure. After running a smart rename on the table which there are several dependencies (stored procs, views, etc). The depenencies are gone after running the rename script. The rename does change all the depenant objects correctly, and if I recompile the SP's or resave the views, the dependencies re-appear.

    Is this a known issue or something I could be doing wrong? Any help is appreciated.

    Thanks
    Steve Erbentraut

    Hi Steve,

    I'm not entirely sure what you mean by the "the dependencies disappear". Do you mean information in the sysdepends table? If so, then it is unfortunately an issue with SQL Server. Sysdepends is not a reliable source of dependency information, and in the majority of cases its contents are corrupt. This is actually one reason we use our own dependency mapping engine, and have a product (Dependency Tracker) that finds out and visualizes dependencies that are otherwise difficult to find out.
    Regards,
    Andras
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
    · Share on Twitter
  • erbentrauterbentraut Posts: 7
    When you right click a table and hit show dependencies, that information is gone after the rename. I am assuming that comes from sysdepends table. I was unaware that was an unreliable source, I guess I will just resort to using the dependency tracker.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Unless Microsoft have changed the basic function of sysdepends since SQL Server 2000, the issue is that sysdepends only updates on object creation, not object modification. It's absolutely 100% reliable if you never change anything...
    · Share on Twitter
  • Andras BAndras B Posts: 249
    Brian Donahue wrote:
    Unless Microsoft have changed the basic function of sysdepends since SQL Server 2000, the issue is that sysdepends only updates on object creation, not object modification. It's absolutely 100% reliable if you never change anything...

    Well, not even then :). This is of course another discussion, but you can create stored procedures that reference ones that do not exist yet. Also, any circular dependency (not uncommon at all :() will mess up sys.depends. But Brian is right that sysdepends should be certainly not considered after database schema changes.

    Regards,
    Andras
    András Belokosztolszki, PhD
    Red Gate Software Ltd.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Did I say 100% reliable? I think I meant to say 10% reliable.
    · Share on Twitter
  • MartinSmithMartinSmith Posts: 12
    I find that periodically scripting the database procedures and functions and changing all CREATE statements to ALTER statements serves to recreate the dependencies correctly.
    · Share on Twitter
Sign In or Register to comment.