More on dependecies

It's not possible to drop tables which violate FOREIGN KEY
CONSTRAINTS. SQL Server, however, is completely blase about dropping
tables that are referenced by either sprocs or views.

Short of writing unit tests for every stored procedure and view are
there any tools to test integrity? I suspect that someone will ask if
I've considered the "Tools -> Check Integrity" option. So where is it?kofisarfo@.gmail.com (Kofi) wrote in message news:<5c157557.0405130510.4c98fce@.posting.google.com>...
> It's not possible to drop tables which violate FOREIGN KEY
> CONSTRAINTS. SQL Server, however, is completely blase about dropping
> tables that are referenced by either sprocs or views.
> Short of writing unit tests for every stored procedure and view are
> there any tools to test integrity? I suspect that someone will ask if
> I've considered the "Tools -> Check Integrity" option. So where is it?

Have a look at sp_depends, which uses the sysdepends system table.
However, dependency metadata in MSSQL isn't completely reliable, due
to issues like deferred name resolution (you can create a proc which
references a table you haven't created yet), and dynamic SQL (the
table name may not even be in the procedure). And indeed there may be
SQL code hidden in client applications also.

You can create views WITH SCHEMABINDING, to prevent a table referenced
by the view from being dropped, but there's no equivalent for stored
procedures.

Finally, it could be argued that creating unit tests for all your code
is a good idea anyway.

Simon