June 18, 2002 at 10:05 am
I am using SQL-DMO to generate a script of views and stored procedures in a database. I have got code that scripts all objects in hierarchical dependency order so that when the script is run, views that depend on other views are created successfully. This works very well, as long as the dependancy information on these views is correct and up to date. To resolve this I tried the sp_refreshview stored procedure, and cycled through each view in the database calling this procedure. This didn't help.
However, when I force the views in question to be recompiled by opening them in Enterprise Manager, the dependancy information is correct. So I used Profiler and found out that EM was issueing an ALTER TABLE statement. When I cycle through all the views isssuing ALTER TABLE statements I get the same problem - depandant views do not script in the correct order and the dependancy information in the database is wrong.
Investigating further, I worked out that SQL-DMO uses sp_MSdependancies to enumerate all the dependancies. So I raided some code form this procedure which issues ALTER TABLE statements in dependancy order. This sort of works because it doesn't mess up the dependancies if they are already correct - but is doesn't fix any existing dependancy problems. This makes sense because this solution is circular.
My question is this: surely there is a "DBCC CheckDependancies" or some similar magic that will correct all dependancy information in the database? I have been told that after SQL 6.5 MS no longer guarantees dependancy information. Where can I find more information on this?
June 18, 2002 at 3:52 pm
I feel the last statement was always true as you can create dynamic queries in procedures liek so
EXEC('SELECT * FROM tblX')
and the dependecy will never be marked. There have been several procedures placed on this site that I believe parse the statement in each item (views, triggers, procedures) that can find where a particular item exists. There may be an item out there to generate a dependency mapping that I just haven't seen. Also are you using the SQLDMODep_FullHierarchy call for EnumDependencies?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 19, 2002 at 1:38 am
Yes, I am using SQLDMODep_FullHierarchy in the EnumDependencies - but in the background this makes a call to sp_MSdependancies:
exec sp_MSdependencies null, null, 70141
In turn this procedure uses the sysdepends table to retrieve dependency data for objects. I am still trying to figure out is the data in sysdepends is incorrect, or if the procedure for retrieving dependancy info is not up to the task.
The interesting thing is that if I cycle through the views in alphabetic order and serially issue alter table statements, this loses the dependancy info on some views. This suggests to me that the problem lies in the process that updates sysdepends
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply