Dependencies are Gone after SmartRename

erbentraut

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

Andras B

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

erbentraut

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.

Brian Donahue

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...

Andras B

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

Brian Donahue

Did I say 100% reliable? I think I meant to say 10% reliable.

MartinSmith

I find that periodically scripting the database procedures and functions and changing all CREATE statements to ALTER statements serves to recreate the dependencies correctly.