Database Dependencies

Database Dependencies

Database Dependencies occur when an object in a database will fail to function as defined when another object is modified or removed. For example, a View that is based on a table is dependent on the definition of that table. If the table is modified or deleted then the view will no longer continue to perform as designed.

In some situations the dependencies are more difficult to define because the dependency may exist in different databases. For example, you could write a stored procedure in database A referencing a table in database B. If you drop the table in database B the stored procedure will fail when executed.

The problem is that the bigger your system grows, the harder it is to know and or manage those dependencies. Cross database dependencies exacerbate the problem further.

In versions of SQL Server prior to 2005 we used the stored procedure sp_depends to view dependencies. ap_depends has problems. If objects were not created in the order of dependence then the necessary dependency information was not tracked or reported. It did not track cross database dependencies. These two holes made sp_depends an un-reliable method for identifying dependencies.

Today SQL Server ships with a number of tools tracking object dependency more accurately, and across databases. If this is an area of interest to you follow this link to learn how to take advantage of the new techniques for finding SQL Server object dependencies.

Puzzler

Here is a brain twister for you. What is the purpose of the + in the following SQL?

WHERE ineStatus in ( + ‘P’, ‘O’, ‘D’)

Submit your answer online below or drop me an Email at btaylor@sswug.org.

Cheers,

Ben