In an automatic shipping/deployment system you may find yourself having to determine object/item dependencies, and stipulate a shipping order manually.
For example, when shipping SQL Server code, you might consider the following order of shipping precedence for database scripts:
- Structural creation
- Structural changes
- Static Data
- Stored Procedures
Views that depend on other views have to be shipped in the appropriate order, so you will need to consider this as a factor.
In the background we have a dependency table (sysdepends for SQL 2000 and sys.sql_dependencies for 2005/2008).
For the purposes of this article, I am interested in the creation and omission of records from this table for Stored Procedure dependencies.
So, let us create two simple stored procedures, root and dependent like so:
create procedure root as
create procedure dependent as
So problems anywhere, and the execution of dependent works a treat:
Moreover, when I look at the sys.sql_dependencies table, I see an entry:
from sys.sql_dependencies sd
inner join sys.objects so on so.object_id = sd.object_id
where so.name = 'dependent'
However, when I add these in reverse (I.e. add the dependent Stored Procedure first), I get the following message:
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object ‘dependent’
With both stored procedures compiled, the code still works as before, however no entry is added retrospectively to the SQL table when ‘root’ is compiled after ‘dependent’
Although this has no practical impact with regard to execution, your ability to perform effective impact analysis for testing and refactoring purposes.
Fortunately, on SQL2005 and 2008, this can be remedied by executing the sp_refreshsqlmodule stored procedure.
And there we have it!